<?php

error_reporting(0);
#error_reporting(E_ALL);
#ini_set('display_errors','On');

if (file_exists('/opt/api/rs_flusher/lib/Jobamqp.php'))
	require_once '/opt/api/rs_flusher/lib/Jobamqp.php';
if (file_exists('/opt/api/rs_flusher/lib/php-amqplib/amqp.inc'))
	require_once '/opt/api/rs_flusher/lib/php-amqplib/amqp.inc';

#error_reporting(0);


# MantisConnect - A webservice interface to Mantis Bug Tracker
# Copyright (C) 2004-2012  Victor Boctor - vboctor@users.sourceforge.net
# This program is distributed under dual licensing.  These include
# GPL and a commercial licenses.  Victor Boctor reserves the right to
# change the license of future releases.
# See docs/ folder for more details

require_once( dirname( __FILE__ ) . DIRECTORY_SEPARATOR . 'mc_core.php' );

//if(!defined("COIN_TABLE"))define("COIN_TABLE","coin20120621");
if(!defined("COIN_TABLE"))define("COIN_TABLE",get_last_coin_table());
//if(!defined("COIN_TABLE"))define("COIN_TABLE",get_last_coin_table());
if(!defined("URL_WSDL"))define("URL_WSDL", "http://127.0.0.1/api/wsdl/mantisconnect.php?wsdl");

/**
 * Check if an issue with the given id exists.
 *
 * @param string $p_username  The name of the user trying to access the issue.
 * @param string $p_password  The password of the user.
 * @param integer $p_issue_id  The id of the issue to check.
 * @return boolean  true if there is an issue with the given id, false otherwise.
 */
function mc_issue_exists( $p_username, $p_password, $p_issue_id ) {
	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	if( !bug_exists( $p_issue_id ) ) {
		return false;
	}

	$t_project_id = bug_get_field( $p_issue_id, 'project_id' );
	if( !mci_has_readonly_access( $t_user_id, $t_project_id ) ) {

		// if we return an error here, then we answered the question!
		return false;
	}

	return true;
}

function mc_coba($username, $password){
	$var['coba'] = 'test';
	return serialize($var);
}

/**
 * Get all details about an issue.
 *
 * @param string $p_username  The name of the user trying to access the issue.
 * @param string $p_password  The password of the user.
 * @param integer $p_issue_id  The id of the issue to retrieve.
 * @return Array that represents an IssueData structure
 */
function mc_issue_get( $p_username, $p_password, $p_issue_id ) {
	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	$t_lang = mci_get_user_lang( $t_user_id );

	if( !bug_exists( $p_issue_id ) ) {
		return new soap_fault( 'Client', '', 'Issue does not exist.' );
	}

	$t_project_id = bug_get_field( $p_issue_id, 'project_id' );
	if( !mci_has_readonly_access( $t_user_id, $t_project_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id );
	}

	if( !access_has_bug_level( VIEWER, $p_issue_id, $t_user_id ) ){
	    return mci_soap_fault_access_denied( $t_user_id );
	}

	$t_bug = bug_get( $p_issue_id, true );
	$t_issue_data = array();

	$t_issue_data['id'] = $p_issue_id;
	$t_issue_data['view_state'] = mci_enum_get_array_by_id( $t_bug->view_state, 'view_state', $t_lang );
	$t_issue_data['last_updated'] = timestamp_to_iso8601( $t_bug->last_updated, false );

	$t_issue_data['project'] = mci_project_as_array_by_id( $t_bug->project_id );
	$t_issue_data['category'] = mci_get_category( $t_bug->category_id );
	$t_issue_data['priority'] = mci_enum_get_array_by_id( $t_bug->priority, 'priority', $t_lang );
	$t_issue_data['severity'] = mci_enum_get_array_by_id( $t_bug->severity, 'severity', $t_lang );
	$t_issue_data['status'] = mci_enum_get_array_by_id( $t_bug->status, 'status', $t_lang );

	$t_issue_data['reporter'] = mci_account_get_array_by_id( $t_bug->reporter_id );
	$t_issue_data['summary'] = $t_bug->summary;
	$t_issue_data['version'] = mci_null_if_empty( $t_bug->version );
	$t_issue_data['build'] = mci_null_if_empty( $t_bug->build );
	$t_issue_data['profile_id'] = mci_null_if_empty( $t_bug->profile_id );
	$t_issue_data['platform'] = mci_null_if_empty( $t_bug->platform );
	$t_issue_data['os'] = mci_null_if_empty( $t_bug->os );
	$t_issue_data['os_build'] = mci_null_if_empty( $t_bug->os_build );
	$t_issue_data['reproducibility'] = mci_enum_get_array_by_id( $t_bug->reproducibility, 'reproducibility', $t_lang );
	$t_issue_data['date_submitted'] = timestamp_to_iso8601( $t_bug->date_submitted, false );
	$t_issue_data['sticky'] = $t_bug->sticky;

	$t_issue_data['sponsorship_total'] = $t_bug->sponsorship_total;

	if( !empty( $t_bug->handler_id ) ) {
		$t_issue_data['handler'] = mci_account_get_array_by_id( $t_bug->handler_id );
	}

	$t_issue_data['projection'] = mci_enum_get_array_by_id( $t_bug->projection, 'projection', $t_lang );
	$t_issue_data['eta'] = mci_enum_get_array_by_id( $t_bug->eta, 'eta', $t_lang );

	$t_issue_data['resolution'] = mci_enum_get_array_by_id( $t_bug->resolution, 'resolution', $t_lang );
	$t_issue_data['fixed_in_version'] = mci_null_if_empty( $t_bug->fixed_in_version );
	$t_issue_data['target_version'] = mci_null_if_empty( $t_bug->target_version );
	$t_issue_data['due_date'] = mci_issue_get_due_date( $t_bug );

	$t_issue_data['description'] = $t_bug->description;
	$t_issue_data['steps_to_reproduce'] = mci_null_if_empty( $t_bug->steps_to_reproduce );
	$t_issue_data['additional_information'] = mci_null_if_empty( $t_bug->additional_information );

	$t_issue_data['attachments'] = mci_issue_get_attachments( $p_issue_id );
	$t_issue_data['relationships'] = mci_issue_get_relationships( $p_issue_id, $t_user_id );
	$t_issue_data['notes'] = mci_issue_get_notes( $p_issue_id );
	$t_issue_data['custom_fields'] = mci_issue_get_custom_fields( $p_issue_id );
	$t_issue_data['monitors'] = mci_account_get_array_by_ids( bug_get_monitors ( $p_issue_id ) );
	$t_issue_data['tags'] = mci_issue_get_tags_for_bug_id( $p_issue_id , $t_user_id );

	return $t_issue_data;
}

/**
 * Returns the category name, possibly null if no category is assigned
 *
 * @param int $p_category_id
 * @return string
 */
function mci_get_category( $p_category_id ) {
	if ( $p_category_id == 0 )
		return '';

	return mci_null_if_empty( category_get_name( $p_category_id ) );
}

/**
 *
 * @param BugData $bug
 * @return soapval the value to be encoded as the due date
 */
function mci_issue_get_due_date( $p_bug ) {
	if ( access_has_bug_level( config_get( 'due_date_view_threshold' ), $p_bug->id )  && !date_is_null( $p_bug->due_date ) ) {
		return new soapval( 'due_date', 'xsd:dateTime', timestamp_to_iso8601( $p_bug->due_date, false ) );
	} else {
		return new soapval( 'due_date','xsd:dateTime', null );
	}

}

/**
 * Sets the supplied array of custom field values to the specified issue id.
 *
 * @param $p_issue_id   Issue id to apply custom field values to.
 * @param $p_custom_fields  The array of custom field values as described in the webservice complex types.
 * @param boolean $p_log_insert create history logs for new values
 */
function mci_issue_set_custom_fields( $p_issue_id, &$p_custom_fields, $p_log_insert ) {
	# set custom field values on the submitted issue
	if( isset( $p_custom_fields ) && is_array( $p_custom_fields ) ) {
		foreach( $p_custom_fields as $t_custom_field ) {
			# get custom field id from object ref
			$t_custom_field_id = mci_get_custom_field_id_from_objectref( $t_custom_field['field'] );

			if( $t_custom_field_id == 0 ) {
				return new soap_fault( 'Client', '', 'Custom field ' . $t_custom_field['field']['name'] . ' not found.' );
			}

			# skip if current user doesn't have login access.
			if( !custom_field_has_write_access( $t_custom_field_id, $p_issue_id ) ) {
				continue;
			}

			$t_value = $t_custom_field['value'];

			if( !custom_field_validate( $t_custom_field_id, $t_value ) ) {
				return new soap_fault( 'Client', '', 'Invalid custom field value for field id ' . $t_custom_field_id . ' .');
			}

			if( !custom_field_set_value( $t_custom_field_id, $p_issue_id, $t_value, $p_log_insert  ) ) {
				return new soap_fault( 'Server', '', 'Unable to set custom field value for field id ' . $t_custom_field_id . ' to issue ' . $p_issue_id. ' .' );
			}
		}
	}
}

/**
 * Get the custom field values associated with the specified issue id.
 *
 * @param $p_issue_id   Issue id to get the custom field values for.
 *
 * @return null if no custom field defined for the project that contains the issue, or if no custom
 *              fields are accessible to the current user.
 */
function mci_issue_get_custom_fields( $p_issue_id ) {
	$t_project_id = bug_get_field( $p_issue_id, 'project_id' );

	$t_custom_fields = array();
	$t_related_custom_field_ids = custom_field_get_linked_ids( $t_project_id );

	foreach( $t_related_custom_field_ids as $t_id ) {
		$t_def = custom_field_get_definition( $t_id );

		if( custom_field_has_read_access( $t_id, $p_issue_id ) ) {

			# user has not access to read this custom field.
			$t_value = custom_field_get_value( $t_id, $p_issue_id );
			if( $t_value === false ) {
				continue;
			}

			# return a blank string if the custom field value is undefined
			if( $t_value === null ) {
				$t_value = '';
			}

			$t_custom_field_value = array();
			$t_custom_field_value['field'] = array();
			$t_custom_field_value['field']['id'] = $t_id;
			$t_custom_field_value['field']['name'] = $t_def['name'];
			$t_custom_field_value['value'] = $t_value;

			$t_custom_fields[] = $t_custom_field_value;
		}
	}

	# foreach

	return( count( $t_custom_fields ) == 0 ? null : $t_custom_fields );
}

/**
 * Get the attachments of an issue.
 *
 * @param integer $p_issue_id  The id of the issue to retrieve the attachments for
 * @return Array that represents an AttachmentData structure
 */
function mci_issue_get_attachments( $p_issue_id ) {
	$t_attachment_rows = bug_get_attachments( $p_issue_id );

	if ( $t_attachment_rows == null) {
		return array();
	}

	$t_result = array();
	foreach( $t_attachment_rows as $t_attachment_row ) {
		if ( !file_can_view_bug_attachments( $p_issue_id, (int)$t_attachment_row['user_id'] ) ) {
			continue;
		}
		$t_attachment = array();
		$t_attachment['id'] = $t_attachment_row['id'];
		$t_attachment['filename'] = $t_attachment_row['filename'];
		$t_attachment['size'] = $t_attachment_row['filesize'];
		$t_attachment['content_type'] = $t_attachment_row['file_type'];
		$t_attachment['date_submitted'] = timestamp_to_iso8601( $t_attachment_row['date_added'], false );
		$t_attachment['download_url'] = mci_get_mantis_path() . 'file_download.php?file_id=' . $t_attachment_row['id'] . '&amp;type=bug';
		$t_attachment['user_id'] = $t_attachment_row['user_id'];
		$t_result[] = $t_attachment;
	}

	return $t_result;
}

/**
 * Get the relationships of an issue.
 *
 * @param integer $p_issue_id  The id of the issue to retrieve the relationships for
 * @return Array that represents an RelationShipData structure
 */
function mci_issue_get_relationships( $p_issue_id, $p_user_id ) {
	$t_relationships = array();

	$t_src_relationships = relationship_get_all_src( $p_issue_id );
	foreach( $t_src_relationships as $t_relship_row ) {
		if( access_has_bug_level( config_get( 'mc_readonly_access_level_threshold' ), $t_relship_row->dest_bug_id, $p_user_id ) ) {
			$t_relationship = array();
			$t_reltype = array();
			$t_relationship['id'] = $t_relship_row->id;
			$t_reltype['id'] = $t_relship_row->type;
			$t_reltype['name'] = relationship_get_description_src_side( $t_relship_row->type );
			$t_relationship['type'] = $t_reltype;
			$t_relationship['target_id'] = $t_relship_row->dest_bug_id;
			$t_relationships[] = $t_relationship;
		}
	}

	$t_dest_relationships = relationship_get_all_dest( $p_issue_id );
	foreach( $t_dest_relationships as $t_relship_row ) {
		if( access_has_bug_level( config_get( 'mc_readonly_access_level_threshold' ), $t_relship_row->src_bug_id, $p_user_id ) ) {
			$t_relationship = array();
			$t_relationship['id'] = $t_relship_row->id;
			$t_reltype = array();
			$t_reltype['id'] = relationship_get_complementary_type( $t_relship_row->type );
			$t_reltype['name'] = relationship_get_description_dest_side( $t_relship_row->type );
			$t_relationship['type'] = $t_reltype;
			$t_relationship['target_id'] = $t_relship_row->src_bug_id;
			$t_relationships[] = $t_relationship;
		}
	}

	return (count( $t_relationships ) == 0 ? null : $t_relationships );
}

/**
 * Get all visible notes for a specific issue
 *
 * @param integer $p_issue_id  The id of the issue to retrieve the notes for
 * @return Array that represents an IssueNoteData structure
 */
function mci_issue_get_notes( $p_issue_id ) {
	$t_user_id = auth_get_current_user_id();
	$t_lang = mci_get_user_lang( $t_user_id );
	$t_project_id = bug_get_field( $p_issue_id, 'project_id' );
	$t_user_bugnote_order = 'ASC'; // always get the notes in ascending order for consistency to the calling application.
	$t_has_time_tracking_access = access_has_bug_level( config_get( 'time_tracking_view_threshold' ), $p_issue_id );

	$t_result = array();
	foreach( bugnote_get_all_visible_bugnotes( $p_issue_id, $t_user_bugnote_order, 0 ) as $t_value ) {
		$t_bugnote = array();
		$t_bugnote['id'] = $t_value->id;
		$t_bugnote['reporter'] = mci_account_get_array_by_id( $t_value->reporter_id );
		$t_bugnote['date_submitted'] = timestamp_to_iso8601( $t_value->date_submitted, false );
		$t_bugnote['last_modified'] = timestamp_to_iso8601( $t_value->last_modified, false );
		$t_bugnote['text'] = $t_value->note;
		$t_bugnote['view_state'] = mci_enum_get_array_by_id( $t_value->view_state, 'view_state', $t_lang );
		$t_bugnote['time_tracking'] = $t_has_time_tracking_access ? $t_value->time_tracking : 0;
		$t_bugnote['note_type'] = $t_value->note_type;
		$t_bugnote['note_attr'] = $t_value->note_attr;

		$t_result[] = $t_bugnote;
	}

	return (count( $t_result ) == 0 ? null : $t_result );
}

/**
 * Sets the monitors of the specified issue
 *
 * <p>This functions performs access level checks and only performs operations which would
 * modify the existing monitors list.</p>
 *
 * @param int $p_issue_id the issue id to set the monitors for
 * @param int $p_user_id the user which requests the monitor change
 * @param array $p_monitors An array of arrays with the <em>id</em> field set to the id
 *  of the users which should monitor this issue.
 */
function mci_issue_set_monitors( $p_issue_id , $p_user_id, $p_monitors ) {

    $t_existing_monitors = bug_get_monitors( $p_issue_id );

    $t_monitors = array();
    foreach ( $p_monitors as $t_monitor )
        $t_monitors[] = $t_monitor['id'];

    foreach ( $t_monitors as $t_user_id ) {

    	if ( $p_user_id == $t_user_id ) {
    		if ( ! access_has_bug_level( config_get( 'monitor_bug_threshold' ), $p_issue_id ) )
    		    continue;
	    } else {
	    	if ( !access_has_bug_level( config_get( 'monitor_add_others_bug_threshold' ), $p_issue_id ) )
	    	    continue;
	    }

       if ( in_array( $p_user_id, $t_existing_monitors) )
           continue;

        bug_monitor( $p_issue_id, $t_user_id);
    }

    foreach ( $t_existing_monitors as $t_user_id ) {

    	if ( $p_user_id == $t_user_id ) {
    		if ( ! access_has_bug_level( config_get( 'monitor_bug_threshold' ), $p_issue_id ) )
    		    continue;
	    } else {
	    	if ( !access_has_bug_level( config_get( 'monitor_delete_others_bug_threshold' ), $p_issue_id ) )
	    	    continue;
	    }

        if ( in_array( $p_user_id, $t_monitors) )
            continue;

        bug_unmonitor( $p_issue_id, $t_user_id);
    }

}

/**
 * Get the biggest issue id currently used.
 *
 * @param string $p_username  The name of the user trying to retrieve the information
 * @param string $p_password  The password of the user.
 * @param int    $p_project_id	-1 default project, 0 for all projects, otherwise project id.
 * @return integer  The biggest used issue id.
 */
function mc_issue_get_biggest_id( $p_username, $p_password, $p_project_id ) {
	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	$t_any = defined( 'META_FILTER_ANY' ) ? META_FILTER_ANY : 'any';
	$t_none = defined( 'META_FILTER_NONE' ) ? META_FILTER_NONE : 'none';

	$t_filter = array(
		'show_category' => Array(
			'0' => $t_any,
		),
		'show_severity' => Array(
			'0' => $t_any,
		),
		'show_status' => Array(
			'0' => $t_any,
		),
		'highlight_changed' => 0,
		'reporter_id' => Array(
			'0' => $t_any,
		),
		'handler_id' => Array(
			'0' => $t_any,
		),
		'show_resolution' => Array(
			'0' => $t_any,
		),
		'show_build' => Array(
			'0' => $t_any,
		),
		'show_version' => Array(
			'0' => $t_any,
		),
		'hide_status' => Array(
			'0' => $t_none,
		),
		'user_monitor' => Array(
			'0' => $t_any,
		),
		'dir' => 'DESC',
		'sort' => 'id',
	);

	$t_page_number = 1;
	$t_per_page = 1;
	$t_bug_count = 0;
	$t_page_count = 0;

	# Get project id, if -1, then retrieve the current which will be the default since there is no cookie.
	$t_project_id = $p_project_id;
	if( $t_project_id == -1 ) {
		$t_project_id = helper_get_current_project();
	}

	if(( $t_project_id > 0 ) && !project_exists( $t_project_id ) ) {
		return new soap_fault( 'Client', '', "Project '$t_project_id' does not exist." );
	}

	if( !mci_has_readonly_access( $t_user_id, $t_project_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id );
	}

	$t_rows = filter_get_bug_rows( $t_page_number, $t_per_page, $t_page_count, $t_bug_count, $t_filter, $t_project_id, $t_user_id );
	if( count( $t_rows ) == 0 ) {
		return 0;
	} else {
		return $t_rows[0]->id;
	}
}

/**
 * Get the id of an issue via the issue's summary.
 *
 * @param string $p_username  The name of the user trying to delete the issue.
 * @param string $p_password  The password of the user.
 * @param string $p_summary  The summary of the issue to retrieve.
 * @return integer  The id of the issue with the given summary, 0 if there is no such issue.
 */
function mc_issue_get_id_from_summary( $p_username, $p_password, $p_summary ) {
	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	$t_bug_table = db_get_table( 'mantis_bug_table' );

	$query = "SELECT id
		FROM $t_bug_table
		WHERE summary = " . db_param();

	$result = db_query_bound( $query, Array( $p_summary ), 1 );

	if( db_num_rows( $result ) == 0 ) {
		return 0;
	} else {
		while(( $row = db_fetch_array( $result ) ) !== false ) {
			$t_issue_id = (int) $row['id'];
			$t_project_id = bug_get_field( $t_issue_id, 'project_id' );

			if( mci_has_readonly_access( $t_user_id, $t_project_id ) &&
				access_has_bug_level( VIEWER, $t_issue_id, $t_user_id ) ) {
				return $t_issue_id;
			}
		}

		// no issue found that belongs to a project that the user has read access to.
		return 0;
	}
}

/**
 * Add an issue to the database.
 *
 * @param string $p_username  The name of the user trying to add the issue.
 * @param string $p_password  The password of the user.
 * @param Array $p_issue  A IssueData structure containing information about the new issue.
 * @return integer  The id of the created issue.
 */
function mc_issue_add( $p_username, $p_password, $p_issue ) {

	global $g_project_override;

	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	$t_project = $p_issue['project'];

	$t_project_id = mci_get_project_id( $t_project );
	$g_project_override = $t_project_id; // ensure that helper_get_current_project() calls resolve to this project id

	if( !mci_has_readwrite_access( $t_user_id, $t_project_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id );
	}

	$t_handler_id = isset( $p_issue['handler'] ) ? mci_get_user_id( $p_issue['handler'] ) : 0;
	$t_priority_id = isset( $p_issue['priority'] ) ? mci_get_priority_id( $p_issue['priority'] ) : config_get( 'default_bug_priority' );
	$t_severity_id = isset( $p_issue['severity'] ) ?  mci_get_severity_id( $p_issue['severity'] ) : config_get( 'default_bug_severity' );
	$t_status_id = isset( $p_issue['status'] ) ? mci_get_status_id( $p_issue['status'] ) : config_get( 'bug_submit_status' );
	$t_reproducibility_id = isset( $p_issue['reproducibility'] ) ?  mci_get_reproducibility_id( $p_issue['reproducibility'] ) : config_get( 'default_bug_reproducibility' );
	$t_resolution_id =  isset( $p_issue['resolution'] ) ? mci_get_resolution_id( $p_issue['resolution'] ) : config_get('default_bug_resolution');
	$t_projection_id = isset( $p_issue['projection'] ) ? mci_get_projection_id( $p_issue['projection'] ) : config_get('default_bug_resolution');
	$t_eta_id = isset( $p_issue['eta'] ) ? mci_get_eta_id( $p_issue['eta'] ) : config_get('default_bug_eta');
	$t_view_state_id = isset( $p_issue['view_state'] ) ?  mci_get_view_state_id( $p_issue['view_state'] ) : config_get( 'default_bug_view_status' );
	$t_reporter_id = isset( $p_issue['reporter'] ) ? mci_get_user_id( $p_issue['reporter'] )  : 0;
	$t_summary = $p_issue['summary'];
	$t_description = $p_issue['description'];
	$t_notes = isset( $p_issue['notes'] ) ? $p_issue['notes'] : array();

	if( $t_reporter_id == 0 ) {
		$t_reporter_id = $t_user_id;
	} else {
		if( $t_reporter_id != $t_user_id ) {

			# Make sure that active user has access level required to specify a different reporter.
			$t_specify_reporter_access_level = config_get( 'mc_specify_reporter_on_add_access_level_threshold' );
			if( !access_has_project_level( $t_specify_reporter_access_level, $t_project_id, $t_user_id ) ) {
				return mci_soap_fault_access_denied( $t_user_id, "Active user does not have access level required to specify a different issue reporter" );
			}
		}
	}

	if(( $t_project_id == 0 ) || !project_exists( $t_project_id ) ) {
		if( $t_project_id == 0 ) {
			return new soap_fault( 'Client', '', "Project '" . $t_project['name'] . "' does not exist." );
		} else {
			return new soap_fault( 'Client', '', "Project with id '" . $t_project_id . "' does not exist." );
		}
	}

	if( !access_has_project_level( config_get( 'report_bug_threshold' ), $t_project_id, $t_user_id ) ) {
		return mci_soap_fault_access_denied( "User '$t_user_id' does not have access right to report issues" );
	}

	#if ( !access_has_project_level( config_get( 'report_bug_threshold' ), $t_project_id ) ||
	#	!access_has_project_level( config_get( 'report_bug_threshold' ), $t_project_id, $v_reporter ) ) {
	#	return new soap_fault( 'Client', '', "User does not have access right to report issues." );
	#}

	if(( $t_handler_id != 0 ) && !user_exists( $t_handler_id ) ) {
		return new soap_fault( 'Client', '', "User '$t_handler_id' does not exist." );
	}

	$t_category = isset ( $p_issue['category'] ) ? $p_issue['category'] : null;

	$t_category_id = translate_category_name_to_id( $t_category, $t_project_id );
	if ( $t_category_id == 0 && !config_get( 'allow_no_category' ) ) {
		if ( !isset( $p_issue['category'] ) || is_blank( $p_issue['category'] ) ) {
			return new soap_fault( 'Client', '', "Category field must be supplied." );
		} else {
			return new soap_fault( 'Client', '', "Category '" . $p_issue['category'] . "' not found for project '$t_project_id'." );
		}
	}

	if ( isset( $p_issue['version'] ) && !is_blank( $p_issue['version'] ) && !version_get_id( $p_issue['version'], $t_project_id ) ) {
		$t_version = $p_issue['version'];

		$t_error_when_version_not_found = config_get( 'mc_error_when_version_not_found' );
		if( $t_error_when_version_not_found == ON ) {
			$t_project_name = project_get_name( $t_project_id );
			return new soap_fault( 'Client', '', "Version '$t_version' does not exist in project '$t_project_name'." );
		} else {
			$t_version_when_not_found = config_get( 'mc_version_when_not_found' );
			$t_version = $t_version_when_not_found;
		}
	}

	if ( is_blank( $t_summary ) ) {
		return new soap_fault( 'Client', '', "Mandatory field 'summary' is missing." );
	}

	if ( is_blank( $t_description ) ) {
		return new soap_fault( 'Client', '', "Mandatory field 'description' is missing." );
	}

	$t_bug_data = new BugData;
	$t_bug_data->profile_id = 0;
	$t_bug_data->project_id = $t_project_id;
	$t_bug_data->reporter_id = $t_reporter_id;
	$t_bug_data->handler_id = $t_handler_id;
	$t_bug_data->priority = $t_priority_id;
	$t_bug_data->severity = $t_severity_id;
	$t_bug_data->reproducibility = $t_reproducibility_id;
	$t_bug_data->status = $t_status_id;
	$t_bug_data->resolution = $t_resolution_id;
	$t_bug_data->projection = $t_projection_id;
	$t_bug_data->category_id = $t_category_id;
	$t_bug_data->date_submitted = isset( $p_issue['date_submitted'] ) ? $p_issue['date_submitted'] : '';
	$t_bug_data->last_updated = isset( $p_issue['last_updated'] ) ? $p_issue['last_updated'] : '';
	$t_bug_data->eta = $t_eta_id;
	$t_bug_data->profile_id = isset ( $p_issue['profile_id'] ) ? $p_issue['profile_id'] : 0;
	$t_bug_data->os = isset( $p_issue['os'] ) ? $p_issue['os'] : '';
	$t_bug_data->os_build = isset( $p_issue['os_build'] ) ? $p_issue['os_build'] : '';
	$t_bug_data->platform = isset( $p_issue['platform'] ) ? $p_issue['platform'] : '';
	$t_bug_data->version = isset( $p_issue['version'] ) ? $p_issue['version'] : '';
	$t_bug_data->fixed_in_version = isset( $p_issue['fixed_in_version'] ) ? $p_issue['fixed_in_version'] : '';
	$t_bug_data->build = isset( $p_issue['build'] ) ? $p_issue['build'] : '';
	$t_bug_data->view_state = $t_view_state_id;
	$t_bug_data->summary = $t_summary;
	$t_bug_data->sponsorship_total = isset( $p_issue['sponsorship_total'] ) ? $p_issue['sponsorship_total'] : 0;
	if (  isset ( $p_issue['sticky']) &&
	     access_has_project_level( config_get( 'set_bug_sticky_threshold', null, null, $t_project_id ), $t_project_id ) ) {
	    $t_bug_data->sticky = $p_issue['sticky'];
	}

	if ( isset( $p_issue['due_date'] ) && access_has_global_level( config_get( 'due_date_update_threshold' ) ) ) {
		$t_bug_data->due_date = mci_iso8601_to_timestamp( $p_issue['due_date'] );
	} else {
		$t_bug_data->due_date = date_get_null();
	}

	if( access_has_project_level( config_get( 'roadmap_update_threshold' ), $t_bug_data->project_id, $t_user_id ) ) {
		$t_bug_data->target_version = isset( $p_issue['target_version'] ) ? $p_issue['target_version'] : '';
	}

	# omitted:
	# var $bug_text_id
	# $t_bug_data->profile_id;
	# extended info
	$t_bug_data->description = $t_description;
	$t_bug_data->steps_to_reproduce = isset( $p_issue['steps_to_reproduce'] ) ? $p_issue['steps_to_reproduce'] : '';
	$t_bug_data->additional_information = isset( $p_issue['additional_information'] ) ? $p_issue['additional_information'] : '';

	# submit the issue
	$t_issue_id = $t_bug_data->create();
	#error_log("\n issue id create " . $t_issue_id ."\n". print_r($p_issue['custom_fields'],1), 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;

	mci_issue_set_custom_fields( $t_issue_id, $p_issue['custom_fields'], false );

        #error_log("\n set custom field ", 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;

	if($t_project_id!=2)
		$p_issue['monitors'] = mc_get_monitoring($t_project_id);

	if ( isset ( $p_issue['monitors'] ) )
	   mci_issue_set_monitors( $t_issue_id , $t_user_id, $p_issue['monitors'] );

	if( isset( $t_notes ) && is_array( $t_notes ) ) {
		foreach( $t_notes as $t_note ) {
			if( isset( $t_note['view_state'] ) ) {
				$t_view_state = $t_note['view_state'];
			} else {
				$t_view_state = config_get( 'default_bugnote_view_status' );
			}

			$note_type = isset ( $t_note['note_type'] ) ? (int) $t_note['note_type'] : BUGNOTE;
			$note_attr = isset ( $t_note['note_type'] ) ? $t_note['note_attr'] : '';

			$t_view_state_id = mci_get_enum_id_from_objectref( 'view_state', $t_view_state );
			bugnote_add( $t_issue_id, $t_note['text'], mci_get_time_tracking_from_note( $t_issue_id, $t_note ), $t_view_state_id == VS_PRIVATE, $note_type, $note_attr, $t_user_id, FALSE );
		}
	}

        #error_log("\n set note ", 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;

	if ( isset ( $p_issue['tags']) && is_array ( $p_issue['tags']) ) {
		mci_tag_set_for_issue( $t_issue_id, $p_issue['tags'], $t_user_id );
	}

        #error_log("\n set tag ", 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;

	email_new_bug( $t_issue_id );

	//if($t_project_id=="1")
	//	proposed_to_close_master($t_project_id);

        #error_log("\n email ", 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;

	return $t_issue_id;
}

function proposed_to_close_master($p_username, $p_password, $ticketID){

	$queryField = "SELECT rel.`destination_bug_id` , bug.status, (
			SELECT  `source_bug_id` 
			FROM  `mantis_bug_relationship_table` a
			LEFT JOIN mantis_custom_field_string_table b ON a.destination_bug_id = b.bug_id
			WHERE b.field_id =28
			AND b.`value` =  '$ticketID'
			AND a.`relationship_type` =2
			) AS parent
			FROM  `mantis_bug_relationship_table` rel
			LEFT JOIN mantis_bug_table bug ON bug.id = rel.`destination_bug_id` 
			WHERE rel.`source_bug_id` = ( 
			SELECT  `source_bug_id` 
			FROM  `mantis_bug_relationship_table` a
			LEFT JOIN mantis_custom_field_string_table b ON a.destination_bug_id = b.bug_id
			WHERE b.field_id =28
			AND b.`value` =  '$ticketID'
			AND a.`relationship_type` =2 ) 
			AND rel.`relationship_type` =2";
	$fieldResult = db_query_bound($queryField);
	
	while( $bugField = db_fetch_array( $fieldResult ) ) {
		if($bugField['status']>=80){
			$ptc[] = "true";
			$parent = $bugField['parent'];
		}else{
			$ptc[] = "false";
		}
	}
	
	if(!in_array("false", $ptc)){
		$queryUpdate = "UPDATE mantis_bug_table SET status = '80'
							WHERE id = '".$parent."'
						";
		$result2 = db_query_bound( $queryUpdate);
		db_result( $result2 );
	}

	return "true";
}

/**
 * Update Issue in database
 *
 * Created By KGB
 * @param string $p_username The name of the user trying to add the issue.
 * @param string $p_password The password of the user.
 * @param Array $p_issue A IssueData structure containing information about the new issue.
 * @return integer The id of the created issue.
 */

function mc_issue_update( $p_username, $p_password, $p_issue_id, $p_issue ) {
	global $g_project_override;

	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	if( !bug_exists( $p_issue_id ) ) {
		return new soap_fault( 'Client', '', "Issue '$p_issue_id' does not exist." );
	}

	$t_project_id = bug_get_field( $p_issue_id, 'project_id' );

	if( !mci_has_readwrite_access( $t_user_id, $t_project_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id );
	}

	$g_project_override = $t_project_id; // ensure that helper_get_current_project() calls resolve to this project id

	$t_project_id = mci_get_project_id( $p_issue['project'] );
	//set reporter before
	// ** QUERY TO FIND OTHER CUSTOM FIELD DATA
        $customQuery = "SELECT `reporter_id` FROM `mantis_bug_table` WHERE `id` = '$p_issue_id'";
        $customResult = db_query_bound($customQuery);
        $t_user_id    = db_result( $customResult );

	//set reporter before
	$t_reporter_id = isset( $p_issue['reporter'] ) ? mci_get_user_id( $p_issue['reporter'] )  : $t_user_id ;
	$t_handler_id = isset( $p_issue['handler'] ) ? mci_get_user_id( $p_issue['handler'] ) : 0;
	$t_project = $p_issue['project'];
	$t_summary = isset( $p_issue['summary'] ) ? $p_issue['summary'] : '';
	$t_description = isset( $p_issue['description'] ) ? $p_issue['description'] : '';


	if(( $t_project_id == 0 ) || !project_exists( $t_project_id ) ) {
		if( $t_project_id == 0 ) {
			return new soap_fault( 'Client', '', "Project '" . $t_project['name'] . "' does not exist." );
		}
		return new soap_fault( 'Client', '', "Project '$t_project_id' does not exist." );
	}

	if( !access_has_bug_level( config_get( 'update_bug_threshold' ), $p_issue_id, $t_user_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id,  "Not enough rights to update issues" );
	}

	if(( $t_handler_id != 0 ) && !user_exists( $t_handler_id ) ) {
		return new soap_fault( 'Client', '', "User '$t_handler_id' does not exist." );
	}

	$t_category = isset ( $p_issue['category'] ) ? $p_issue['category'] : null;

	$t_category_id = translate_category_name_to_id( $t_category, $t_project_id );
	if ( $t_category_id == 0 && !config_get( 'allow_no_category' ) ) {
		if ( isset( $p_issue['category'] ) && !is_blank( $p_issue['category'] ) ) {
			return new soap_fault( 'Client', '', "Category field must be supplied." );
		} else {
			return new soap_fault( 'Client', '', "Category '" . $p_issue['category'] . "' not found for project '$t_project_name'." );
		}
	}

	if ( isset( $p_issue['version'] ) && !is_blank( $p_issue['version'] ) && !version_get_id( $p_issue['version'], $t_project_id ) ) {
		$t_error_when_version_not_found = config_get( 'mc_error_when_version_not_found' );
		if( $t_error_when_version_not_found == ON ) {
			$t_project_name = project_get_name( $t_project_id );
			return new soap_fault( 'Client', '', "Version '" . $p_issue['version'] . "' does not exist in project '$t_project_name'." );
		} else {
			$t_version_when_not_found = config_get( 'mc_version_when_not_found' );
			$p_issue['version'] = $t_version_when_not_found;
		}
	}

	if ( is_blank( $t_summary ) ) {
		return new soap_fault( 'Client', '', "Mandatory field 'summary' is missing." );
	}

	if ( is_blank( $t_description ) ) {
		return new soap_fault( 'Client', '', "Mandatory field 'description' is missing." );
	}

	// fields which we expect to always be set
	$t_bug_data = bug_get( $p_issue_id, true );
	$t_bug_data->project_id = $t_project_id;
	$t_bug_data->reporter_id = $t_reporter_id;
	if($t_handler_id!=0)
		$t_bug_data->handler_id = $t_handler_id;
	$t_bug_data->category_id = $t_category_id;
	$t_bug_data->summary = $t_summary;
	$t_bug_data->description = $t_description;

	// fields which might not be set
	if ( isset ( $p_issue['steps_to_reproduce'] ) )
		$t_bug_data->steps_to_reproduce = $p_issue['steps_to_reproduce'];
	if ( isset ( $p_issue['additional_information'] ) )
		$t_bug_data->additional_information = $p_issue['additional_information'];
	if ( isset( $p_issue['priority'] ) )
		$t_bug_data->priority = mci_get_priority_id( $p_issue['priority'] );
	if ( isset( $p_issue['severity'] ) )
		$t_bug_data->severity = mci_get_severity_id( $p_issue['severity'] );
	if ( isset( $p_issue['status'] ) )
		$t_bug_data->status = mci_get_status_id ( $p_issue['status'] );
	if ( isset ( $p_issue['reproducibility'] ) )
		$t_bug_data->reproducibility = mci_get_reproducibility_id( $p_issue['reproducibility'] );
	if ( isset ( $p_issue['resolution'] ) )
		$t_bug_data->resolution = mci_get_resolution_id( $p_issue['resolution'] );
	if ( isset ( $p_issue['projection'] ) )
		$t_bug_data->projection = mci_get_projection_id( $p_issue['projection'] );
	if ( isset ( $p_issue['eta'] ) )
		$t_bug_data->eta = mci_get_eta_id( $p_issue['eta'] );
	if ( isset ( $p_issue['view_state'] ) )
		$t_bug_data->view_state = mci_get_view_state_id( $p_issue['view_state'] );
	if ( isset ( $p_issue['date_submitted'] ) )
		$t_bug_data->date_submitted = $p_issue['date_submitted'];
	if ( isset ( $p_issue['date_updated'] ) )
		$t_bug_data->last_updated = $p_issue['last_updated'];
	if ( isset ( $p_issue['profile_id'] ) )
		$t_bug_data->profile_id = $p_issue['profile_id'];
	if ( isset ( $p_issue['os'] ) )
		$t_bug_data->os = $p_issue['os'];
	if ( isset ( $p_issue['os_build'] ) )
		$t_bug_data->os_build = $p_issue['os_build'];
	if ( isset ( $p_issue['build'] ) )
		$t_bug_data->build = $p_issue['build'];
	if ( isset ( $p_issue['platform'] ) )
		$t_bug_data->platform = $p_issue['platform'];
	if ( isset ( $p_issue['version'] ) )
		$t_bug_data->version = $p_issue['version'];
	if ( isset ( $p_issue['fixed_in_version'] ) )
		$t_bug_data->fixed_in_version = $p_issue['fixed_in_version'];
	if (  isset ( $p_issue['sticky']) && access_has_bug_level( config_get( 'set_bug_sticky_threshold' ), $t_bug_data->id ) ) {
	    $t_bug_data->sticky = $p_issue['sticky'];
	}

	if ( isset( $p_issue['due_date'] ) && access_has_global_level( config_get( 'due_date_update_threshold' ) ) ) {
		$t_bug_data->due_date = mci_iso8601_to_timestamp( $p_issue['due_date'] );
	} else {
		$t_bug_data->due_date = date_get_null();
	}

	if( access_has_project_level( config_get( 'roadmap_update_threshold' ), $t_bug_data->project_id, $t_user_id ) ) {
		$t_bug_data->target_version = isset( $p_issue['target_version'] ) ? $p_issue['target_version'] : '';
	}

	mci_issue_set_custom_fields( $p_issue_id, $p_issue['custom_fields'], true );
	if ( isset ( $p_issue['monitors'] ) )
	    mci_issue_set_monitors( $p_issue_id , $t_user_id, $p_issue['monitors'] );

	if ( isset( $p_issue['notes'] ) && is_array( $p_issue['notes'] ) ) {

		$t_bugnotes = bugnote_get_all_visible_bugnotes( $p_issue_id, 'DESC', 0 );
		$t_bugnotes_by_id = array();
		foreach ( $t_bugnotes as $t_bugnote ) {
			$t_bugnotes_by_id[$t_bugnote->id] = $t_bugnote;
		}

		foreach ( $p_issue['notes'] as $t_note ) {
			if ( isset( $t_note['view_state'] ) ) {
				$t_view_state = $t_note['view_state'];
			} else {
				$t_view_state = config_get( 'default_bugnote_view_status' );
			}

			if ( isset( $t_note['id'] ) && ( (int)$t_note['id'] > 0 ) ) {
				$t_bugnote_id = (integer)$t_note['id'];

				$t_view_state_id = mci_get_enum_id_from_objectref( 'view_state', $t_view_state );

				if ( array_key_exists( $t_bugnote_id , $t_bugnotes_by_id) ) {

					$t_bugnote_changed = false;

					if ( $t_bugnote->note !== $t_note['text']) {
						bugnote_set_text( $t_bugnote_id, $t_note['text'] );
						$t_bugnote_changed = true;
					}

					if ( $t_bugnote->view_state !== $t_view_state_id ) {
						bugnote_set_view_state( $t_bugnote_id, $t_view_state_id == VS_PRIVATE );
						$t_bugnote_changed = true;
					}

					if ( isset( $t_note['time_tracking']) && $t_note['time_tracking'] !== $t_bugnote->time_tracking ) {
						bugnote_set_time_tracking( $t_bugnote_id, mci_get_time_tracking_from_note( $p_issue_id, $t_note ) );
						$t_bugnote_changed = true;
					}

					if ( $t_bugnote_changed ) {
						bugnote_date_update( $t_bugnote_id );
					}

				}
			} else {
				$t_view_state_id = mci_get_enum_id_from_objectref( 'view_state', $t_view_state );

				$note_type = isset ( $t_note['note_type'] ) ? (int) $t_note['note_type'] : BUGNOTE;
			    $note_attr = isset ( $t_note['note_type'] ) ? $t_note['note_attr'] : '';

				bugnote_add( $p_issue_id, $t_note['text'], mci_get_time_tracking_from_note( $p_issue_id, $t_note ), $t_view_state_id == VS_PRIVATE, $note_type, $note_attr, $t_user_id, FALSE );
			}
		}
	}

	if ( isset ( $p_issue['tags']) && is_array ( $p_issue['tags']) ) {
		mci_tag_set_for_issue( $p_issue_id, $p_issue['tags'] , $t_user_id );
	}

	# submit the issue
	return $t_is_success = $t_bug_data->update( /* update_extended */ true, /* bypass_email */ false);
}

function mc_issue_set_tags ( $p_username, $p_password, $p_issue_id, $p_tags ) {

	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	if( !bug_exists( $p_issue_id ) ) {
		return new soap_fault( 'Client', '', "Issue '$p_issue_id' does not exist." );
	}

	$t_project_id = bug_get_field( $p_issue_id, 'project_id' );

	if( !mci_has_readwrite_access( $t_user_id, $t_project_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id );
	}

	mci_tag_set_for_issue( $p_issue_id,  $p_tags, $t_user_id );

	return true;
}

/**
 * Delete the specified issue.
 *
 * @param string $p_username  The name of the user trying to delete the issue.
 * @param string $p_password  The password of the user.
 * @param integer $p_issue_id  The id of the issue to delete.
 * @return boolean  True if the issue has been deleted successfully, false otherwise.
 */
function mc_issue_delete( $p_username, $p_password, $p_issue_id ) {
	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	if( !bug_exists( $p_issue_id ) ) {
		return new soap_fault( 'Client', '', "Issue '$p_issue_id' does not exist.");
	}

	$t_project_id = bug_get_field( $p_issue_id, 'project_id' );
	if( !mci_has_readwrite_access( $t_user_id, $t_project_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id );
	}

	if ( !access_has_bug_level( config_get( 'delete_bug_threshold' ), $p_issue_id, $t_user_id ) ) {
	    return mci_soap_fault_access_denied( $t_user_id );
	}

	return bug_delete( $p_issue_id );
}

/**
 * Add a note to an existing issue.
 *
 * @param string $p_username  The name of the user trying to add a note to an issue.
 * @param string $p_password  The password of the user.
 * @param integer $p_issue_id  The id of the issue to add the note to.
 * @param IssueNoteData $p_note  The note to add.
 * @return integer The id of the added note.
 */
function mc_issue_note_add( $p_username, $p_password, $p_issue_id, $p_note ) {
	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	if( (integer) $p_issue_id < 1 ) {
		return new soap_fault( 'Client', '', "Invalid issue id '$p_issue_id'" );
	}

	if( !bug_exists( $p_issue_id ) ) {
		return new soap_fault( 'Client', '', "Issue '$p_issue_id' does not exist." );
	}

	if ( !isset( $p_note['text'] ) || is_blank( $p_note['text'] ) ) {
		return new soap_fault( 'Client', '', "Issue note text must not be blank." );
	}

	$t_project_id = bug_get_field( $p_issue_id, 'project_id' );
	if( !mci_has_readwrite_access( $t_user_id, $t_project_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id );
	}

	if( !access_has_bug_level( config_get( 'add_bugnote_threshold' ), $p_issue_id, $t_user_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id, "You do not have access rights to add notes to this issue" );
	}

	if( bug_is_readonly( $p_issue_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id, "Issue '$p_issue_id' is readonly" );
	}

	if( isset( $p_note['view_state'] ) ) {
		$t_view_state = $p_note['view_state'];
	} else {
		$t_view_state = array(
			'id' => config_get( 'default_bug_view_status' ),
		);
	}

	$t_view_state_id = mci_get_enum_id_from_objectref( 'view_state', $t_view_state );

	$note_type = isset ( $p_note['note_type'] ) ? (int) $p_note['note_type'] : BUGNOTE;
	$note_attr = isset ( $p_note['note_type'] ) ? $p_note['note_attr'] : '';

	return bugnote_add( $p_issue_id, $p_note['text'], mci_get_time_tracking_from_note( $p_issue_id, $p_note ), $t_view_state_id == VS_PRIVATE, $note_type, $note_attr, $t_user_id );
}

/**
 * Update a bugnote time tracking counter
 * erad 20120409
 *
 * @param string $p_username  The name of the user trying to add a note to an issue.
 * @param string $p_password  The password of the user.
 * @param int $p_issue_note_id
 * @param string $dummy
 * @param string $p_bugnote_time_tracking_type
 * @return bool
*/
function mc_issue_note_time_tracking_update( $p_username, $p_password, $p_issue_note_id, $dummy, $p_tracking_type ) {
	// aneh, kenapa parameter ke-4 ($dummy) selalu tipe datanya kebaca sebagai integer, udah diedit wsdl-nya juga ga ngaruh
	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	if( (integer) $p_issue_note_id < 1 ) {
		return new soap_fault( 'Client', '', "Invalid issue note id '$p_issue_note_id'.");
	}

	if( !bugnote_exists( $p_issue_note_id ) ) {
		return new soap_fault( 'Client', '', "Issue note '$p_issue_note_id' does not exist.");
	}

	$p_tracking_type = trim(strtolower($p_tracking_type));
	if ($p_tracking_type != "start" && $p_tracking_type != "end") {
		return new soap_fault( 'Client', '', "Tracking type '$p_tracking_type' does not exist.");
	}

	$t_issue_id = bugnote_get_field( $p_issue_note_id, 'bug_id' );
	$t_project_id = bug_get_field( $t_issue_id, 'project_id' );
    if( !mci_has_readwrite_access( $t_user_id, $t_project_id ) ) {
        return mci_soap_fault_access_denied( $t_user_id );
    }

    if( !access_has_bug_level( config_get( 'add_bugnote_threshold' ), $t_issue_id, $t_user_id ) ) {
        return mci_soap_fault_access_denied( $t_user_id, "You do not have access rights to add notes to this issue" );
    }

    if( bug_is_readonly( $t_issue_id ) ) {
        return mci_soap_fault_access_denied( $t_user_id, "Issue ' . $t_issue_id . ' is readonly" );
    }

    if( isset( $p_note['view_state'] )) {
        $t_view_state = $p_note['view_state'];
        $t_view_state_id = mci_get_enum_id_from_objectref( 'view_state', $t_view_state );
        bugnote_set_view_state( $t_issue_note_id, $t_view_state_id );
    }

	return bugnote_time_tracking_update( $p_issue_note_id, $p_tracking_type );
}

/**
 * Delete a note given its id.
 *
 * @param string $p_username  The name of the user trying to add a note to an issue.
 * @param string $p_password  The password of the user.
 * @param integer $p_issue_note_id  The id of the note to be deleted.
 * @return true: success, false: failure
 */
function mc_issue_note_delete( $p_username, $p_password, $p_issue_note_id ) {
	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	if( (integer) $p_issue_note_id < 1 ) {
		return new soap_fault( 'Client', '', "Invalid issue note id '$p_issue_note_id'.");
	}

	if( !bugnote_exists( $p_issue_note_id ) ) {
		return new soap_fault( 'Client', '', "Issue note '$p_issue_note_id' does not exist.");
	}

	$t_issue_id = bugnote_get_field( $p_issue_note_id, 'bug_id' );
	$t_project_id = bug_get_field( $t_issue_id, 'project_id' );
	if( !mci_has_readwrite_access( $t_user_id, $t_project_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id );
	}

	$t_reporter_id = bugnote_get_field( $p_issue_note_id, 'reporter_id' );

	// mirrors check from bugnote_delete.php
	if ( ( $t_user_id != $t_reporter_id ) || ( OFF == config_get( 'bugnote_allow_user_edit_delete' ) ) ) {
	    if ( !access_has_bugnote_level( config_get( 'delete_bugnote_threshold' ), $p_issue_note_id ) ) {
	        return mci_soap_fault_access_denied( $t_user_id );
	    }
	}

	return bugnote_delete( $p_issue_note_id );
}

/**
 * Update a note
 *
 * @param string $p_username  The name of the user trying to add a note to an issue.
 * param string $p_password  The password of the user.
 * @param IssueNoteData $p_note  The note to update.
 * @return true on success, false on failure
 */
function mc_issue_note_update( $p_username, $p_password, $p_note ) {
    $t_user_id = mci_check_login( $p_username, $p_password );

    if( $t_user_id === false ) {
        return mci_soap_fault_login_failed();
    }

    if ( !isset( $p_note['id'] ) || is_blank( $p_note['id'] ) ) {
        return new soap_fault( 'Client', '', "Issue id must not be blank." );
    }

    if ( !isset( $p_note['text'] ) || is_blank( $p_note['text'] ) ) {
        return new soap_fault( 'Client', '', "Issue note text must not be blank." );
    }

    $t_issue_note_id = $p_note['id'];

    if( !bugnote_exists( $t_issue_note_id ) ) {
        return new soap_fault( 'Server', '', "Issue note '$t_issue_note_id' does not exist." );
    }

	$t_issue_id = bugnote_get_field( $t_issue_note_id, 'bug_id' );

	$t_project_id = bug_get_field( $t_issue_id, 'project_id' );

    if( !mci_has_readwrite_access( $t_user_id, $t_project_id ) ) {
        return mci_soap_fault_access_denied( $t_user_id );
    }

    if( !access_has_bug_level( config_get( 'add_bugnote_threshold' ), $t_issue_id, $t_user_id ) ) {
        return mci_soap_fault_access_denied( $t_user_id, "You do not have access rights to add notes to this issue" );
    }

    if( bug_is_readonly( $t_issue_id ) ) {
        return mci_soap_fault_access_denied( $t_user_id, "Issue ' . $t_issue_id . ' is readonly" );
    }

    if( isset( $p_note['view_state'] )) {
        $t_view_state = $p_note['view_state'];
        $t_view_state_id = mci_get_enum_id_from_objectref( 'view_state', $t_view_state );
        bugnote_set_view_state( $t_issue_note_id, $t_view_state_id );
    }

    bugnote_set_text( $t_issue_note_id, $p_note['text'] );

    return bugnote_date_update( $t_issue_note_id );
}

/**
 * Submit a new relationship.
 *
 * @param string $p_username  The name of the user trying to add a note to an issue.
 * @param string $p_password  The password of the user.
 * @param integer $p_issue_id  The id of the issue of the source issue.
 * @param RelationshipData $p_relationship  The relationship to add.
 * @return integer The id of the added relationship.
 */
function mc_issue_relationship_add( $p_username, $p_password, $p_issue_id, $p_relationship ) {
	$t_user_id = mci_check_login( $p_username, $p_password );
	$t_dest_issue_id = $p_relationship['target_id'];
	$t_rel_type = $p_relationship['type'];

	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	$t_project_id = bug_get_field( $p_issue_id, 'project_id' );
	if( !mci_has_readwrite_access( $t_user_id, $t_project_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id );
	}

	# user has access to update the bug...
	if( !access_has_bug_level( config_get( 'update_bug_threshold' ), $p_issue_id, $t_user_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id, "Active user does not have access level required to add a relationship to this issue" );
	}

	# source and destination bugs are the same bug...
	if( $p_issue_id == $t_dest_issue_id ) {
		return new soap_fault( 'Client', '', "An issue can't be related to itself." );
	}

	# the related bug exists...
	if( !bug_exists( $t_dest_issue_id ) ) {
		return new soap_fault( 'Client', '', "Issue '$t_dest_issue_id' not found." );
	}

	# bug is not read-only...
	if( bug_is_readonly( $p_issue_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id, "Issue '$p_issue_id' is readonly" );
	}

	# user can access to the related bug at least as viewer...
	if( !access_has_bug_level( VIEWER, $t_dest_issue_id, $t_user_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id, "The issue '$t_dest_issue_id' requires higher access level" );
	}

	$t_old_id_relationship = relationship_same_type_exists( $p_issue_id, $t_dest_issue_id, $t_rel_type['id'] );

	if( $t_old_id_relationship == 0 ) {
		relationship_add( $p_issue_id, $t_dest_issue_id, $t_rel_type['id'] );

		// The above function call into MantisBT does not seem to return a valid BugRelationshipData object.
		// So we call db_insert_id in order to find the id of the created relationship.
		$t_relationship_id = db_insert_id( db_get_table( 'mantis_bug_relationship_table' ) );

		# Add log line to the history (both bugs)
		history_log_event_special( $p_issue_id, BUG_ADD_RELATIONSHIP, $t_rel_type['id'], $t_dest_issue_id );
		history_log_event_special( $t_dest_issue_id, BUG_ADD_RELATIONSHIP, relationship_get_complementary_type( $t_rel_type['id'] ), $p_issue_id );

		# update bug last updated for both bugs
		bug_update_date( $p_issue_id );
		bug_update_date( $t_dest_issue_id );

		# send email notification to the users addressed by both the bugs
		email_relationship_added( $p_issue_id, $t_dest_issue_id, $t_rel_type['id'] );
		email_relationship_added( $t_dest_issue_id, $p_issue_id, relationship_get_complementary_type( $t_rel_type['id'] ) );

		return $t_relationship_id;
	} else {
		return new soap_fault( 'Client', '', "Relationship already exists." );
	}
}

/**
 * Delete the relationship with the specified target id.
 *
 * @param string $p_username  The name of the user trying to add a note to an issue.
 * @param string $p_password  The password of the user.
 * @param integer $p_issue_id  The id of the source issue for the relationship
 * @param integer $p_relationship_id  The id of relationship to delete.
 * @return true: success, false: failure
 */
function mc_issue_relationship_delete( $p_username, $p_password, $p_issue_id, $p_relationship_id ) {
	$t_user_id = mci_check_login( $p_username, $p_password );

	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	$t_project_id = bug_get_field( $p_issue_id, 'project_id' );
	if( !mci_has_readwrite_access( $t_user_id, $t_project_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id );
	}

	# user has access to update the bug...
	if( !access_has_bug_level( config_get( 'update_bug_threshold' ), $p_issue_id, $t_user_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id , "Active user does not have access level required to remove a relationship from this issue." );
	}

	# bug is not read-only...
	if( bug_is_readonly( $p_issue_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id , "Issue '$p_issue_id' is readonly." );
	}

	# retrieve the destination bug of the relationship
	$t_dest_issue_id = relationship_get_linked_bug_id( $p_relationship_id, $p_issue_id );

	# user can access to the related bug at least as viewer, if it's exist...
	if( bug_exists( $t_dest_issue_id ) ) {
		if( !access_has_bug_level( VIEWER, $t_dest_issue_id, $t_user_id ) ) {
			return mci_soap_fault_access_denied( $t_user_id , "The issue '$t_dest_issue_id' requires higher access level." );
		}
	}

	$t_bug_relationship_data = relationship_get( $p_relationship_id );
	$t_rel_type = $t_bug_relationship_data->type;

	# delete relationship from the DB
	relationship_delete( $p_relationship_id );

	# update bug last updated
	bug_update_date( $p_issue_id );
	bug_update_date ( $t_dest_issue_id );

	# set the rel_type for both bug and dest_bug based on $t_rel_type and on who is the dest bug
	if( $p_issue_id == $t_bug_relationship_data->src_bug_id ) {
		$t_bug_rel_type = $t_rel_type;
		$t_dest_bug_rel_type = relationship_get_complementary_type( $t_rel_type );
	} else {
		$t_bug_rel_type = relationship_get_complementary_type( $t_rel_type );
		$t_dest_bug_rel_type = $t_rel_type;
	}

	# send email and update the history for the src issue
	history_log_event_special( $p_issue_id, BUG_DEL_RELATIONSHIP, $t_bug_rel_type, $t_dest_issue_id );
	email_relationship_deleted( $p_issue_id, $t_dest_issue_id, $t_bug_rel_type );

	if( bug_exists( $t_dest_issue_id ) ) {

		# send email and update the history for the dest issue
		history_log_event_special( $t_dest_issue_id, BUG_DEL_RELATIONSHIP, $t_dest_bug_rel_type, $p_issue_id );
		email_relationship_deleted( $t_dest_issue_id, $p_issue_id, $t_dest_bug_rel_type );
	}

	return true;
}

/**
 * Log a checkin event on the issue
 *
 * @param string $p_username  The name of the user trying to access the issue.
 * @param string $p_password  The password of the user.
 * @param integer $p_issue_id The id of the issue to log a checkin.
 * @param string $p_comment   The comment to add
 * @param boolean $p_fixed    True if the issue is to be set to fixed
 * @return boolean  true success, false otherwise.
 */
function mc_issue_checkin( $p_username, $p_password, $p_issue_id, $p_comment, $p_fixed ) {
	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	if( !bug_exists( $p_issue_id ) ) {
		return new soap_fault( 'Client', '', "Issue '$p_issue_id' not found." );
	}

	$t_project_id = bug_get_field( $p_issue_id, 'project_id' );
	if( !mci_has_readwrite_access( $t_user_id, $t_project_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id );
	}

	helper_call_custom_function( 'checkin', array( $p_issue_id, $p_comment, '', '', $p_fixed ) );

	return true;
}

/**
 * Returns the date in iso8601 format, with proper timezone offset applied
 *
 * @param string $p_date the date in iso8601 format
 * @return int the timestamp
 */
function mci_iso8601_to_timestamp( $p_date ) {

	// retrieve the offset, seems to be lost by nusoap
	$t_utc_date = new DateTime( $p_date, new DateTimeZone( 'UTC' ) );
	$t_timezone = new DateTimeZone( date_default_timezone_get() );
	$t_offset = $t_timezone->getOffset( $t_utc_date );

	$t_raw_timestamp = iso8601_to_timestamp( $p_date );

	return $t_raw_timestamp - $t_offset;

}

/**
 * Returns an array for SOAP encoding from a BugData object
 *
 * @param BugData $p_issue_data
 * @param int $p_user_id
 * @param string $p_lang
 * @return array The issue as an array
 */
function mci_issue_data_as_array( $p_issue_data, $p_user_id, $p_lang ) {

		$t_id = $p_issue_data->id;

		$t_issue = array();
		$t_issue['id'] = $t_id;
		$t_issue['view_state'] = mci_enum_get_array_by_id( $p_issue_data->view_state, 'view_state', $p_lang );
		$t_issue['last_updated'] = timestamp_to_iso8601( $p_issue_data->last_updated, false );

		$t_issue['project'] = mci_project_as_array_by_id( $p_issue_data->project_id );
		$t_issue['category'] = mci_get_category( $p_issue_data->category_id );
		$t_issue['priority'] = mci_enum_get_array_by_id( $p_issue_data->priority, 'priority', $p_lang );
		$t_issue['severity'] = mci_enum_get_array_by_id( $p_issue_data->severity, 'severity', $p_lang );
		$t_issue['status'] = mci_enum_get_array_by_id( $p_issue_data->status, 'status', $p_lang );

		$t_issue['reporter'] = mci_account_get_array_by_id( $p_issue_data->reporter_id );
		$t_issue['summary'] = $p_issue_data->summary;
		$t_issue['version'] = mci_null_if_empty( $p_issue_data->version );
		$t_issue['build'] = mci_null_if_empty( $p_issue_data->build );
		$t_issue['profile_id'] = mci_null_if_empty( $p_issue_data->profile_id );
		$t_issue['platform'] = mci_null_if_empty( $p_issue_data->platform );
		$t_issue['os'] = mci_null_if_empty( $p_issue_data->os );
		$t_issue['os_build'] = mci_null_if_empty( $p_issue_data->os_build );
		$t_issue['reproducibility'] = mci_enum_get_array_by_id( $p_issue_data->reproducibility, 'reproducibility', $p_lang );
		$t_issue['date_submitted'] = timestamp_to_iso8601( $p_issue_data->date_submitted, false );
		$t_issue['sticky'] = $p_issue_data->sticky;

		$t_issue['sponsorship_total'] = $p_issue_data->sponsorship_total;

		if( !empty( $p_issue_data->handler_id ) ) {
			$t_issue['handler'] = mci_account_get_array_by_id( $p_issue_data->handler_id );
		}
		$t_issue['projection'] = mci_enum_get_array_by_id( $p_issue_data->projection, 'projection', $p_lang );
		$t_issue['eta'] = mci_enum_get_array_by_id( $p_issue_data->eta, 'eta', $p_lang );

		$t_issue['resolution'] = mci_enum_get_array_by_id( $p_issue_data->resolution, 'resolution', $p_lang );
		$t_issue['fixed_in_version'] = mci_null_if_empty( $p_issue_data->fixed_in_version );
		$t_issue['target_version'] = mci_null_if_empty( $p_issue_data->target_version );

		$t_issue['description'] = bug_get_text_field( $t_id, 'description' );

		$t_steps_to_reproduce = bug_get_text_field( $t_id, 'steps_to_reproduce' );
		$t_issue['steps_to_reproduce'] = mci_null_if_empty( $t_steps_to_reproduce );

		$t_additional_information = bug_get_text_field( $t_id, 'additional_information' );
		$t_issue['additional_information'] = mci_null_if_empty( $t_additional_information );

		$t_issue['attachments'] = mci_issue_get_attachments( $p_issue_data->id );
		$t_issue['relationships'] = mci_issue_get_relationships( $p_issue_data->id, $p_user_id );
		$t_issue['notes'] = mci_issue_get_notes( $p_issue_data->id );
		$t_issue['custom_fields'] = mci_issue_get_custom_fields( $p_issue_data->id );
		$t_issue['tags'] = mci_issue_get_tags_for_bug_id( $p_issue_data->id, $p_user_id );

		return $t_issue;
}

function mci_issue_get_tags_for_bug_id( $p_bug_id, $p_user_id ) {

	if ( !access_has_global_level( config_get( 'tag_view_threshold' ), $p_user_id ) )
		return array();

	$t_tag_rows = tag_bug_get_attached( $p_bug_id );
	$t_result = array();

	foreach ( $t_tag_rows as $t_tag_row ) {
		$t_result[] = array (
			'id' => $t_tag_row['id'],
			'name' => $t_tag_row['name']
		);
	}

	return $t_result;
}

/**
 * Returns an array for SOAP encoding from a BugData object
 *
 * @param BugData $p_issue_data
 * @return array The issue header data as an array
 */
function mci_issue_data_as_header_array( $p_issue_data ) {

		$t_issue = array();

		$t_id = $p_issue_data->id;

		$t_issue['id'] = $t_id;
		$t_issue['view_state'] = $p_issue_data->view_state;
		$t_issue['last_updated'] = timestamp_to_iso8601( $p_issue_data->last_updated, false );

		$t_issue['project'] = $p_issue_data->project_id;
		$t_issue['category'] = mci_get_category( $p_issue_data->category_id );
		$t_issue['priority'] = $p_issue_data->priority;
		$t_issue['severity'] = $p_issue_data->severity;
		$t_issue['status'] = $p_issue_data->status;

		$t_issue['reporter'] = $p_issue_data->reporter_id;
		$t_issue['summary'] = $p_issue_data->summary;
		if( !empty( $p_issue_data->handler_id ) ) {
			$t_issue['handler'] = $p_issue_data->handler_id;
		}
		$t_issue['resolution'] = $p_issue_data->resolution;

		$t_issue['attachments_count'] = count( mci_issue_get_attachments( $p_issue_data->id ) );
		$t_issue['notes_count'] = count( mci_issue_get_notes( $p_issue_data->id ) );

		return $t_issue;
}


/*

RAN SHARING

*/


/**
 * Add an NIW to the database.
 *
 * @param string $p_username  The name of the user trying to add the issue.
 * @param string $p_password  The password of the user.
 * @param Array $p_issue  A IssueData structure containing information about the new issue.
 * @return integer  The id of the created issue.
 */
function mc_niw_add( $p_username, $p_password, $p_issue ) {

	global $g_project_override;

	$t_user_id = mci_check_login( $p_username, $p_password );
	if( $t_user_id === false ) {
		return mci_soap_fault_login_failed();
	}

	$t_project = $p_issue['project'];

	$t_project_id = mci_get_project_id( $t_project );
	$g_project_override = $t_project_id; // ensure that helper_get_current_project() calls resolve to this project id

	if( !mci_has_readwrite_access( $t_user_id, $t_project_id ) ) {
		return mci_soap_fault_access_denied( $t_user_id );
	}

	$t_handler_id = isset( $p_issue['handler'] ) ? mci_get_user_id( $p_issue['handler'] ) : 0;
	$t_priority_id = isset( $p_issue['priority'] ) ? mci_get_priority_id( $p_issue['priority'] ) : config_get( 'default_bug_priority' );
	$t_severity_id = isset( $p_issue['severity'] ) ?  mci_get_severity_id( $p_issue['severity'] ) : config_get( 'default_bug_severity' );
	$t_status_id = isset( $p_issue['status'] ) ? mci_get_status_id( $p_issue['status'] ) : config_get( 'bug_submit_status' );
	$t_reproducibility_id = isset( $p_issue['reproducibility'] ) ?  mci_get_reproducibility_id( $p_issue['reproducibility'] ) : config_get( 'default_bug_reproducibility' );
	$t_resolution_id =  isset( $p_issue['resolution'] ) ? mci_get_resolution_id( $p_issue['resolution'] ) : config_get('default_bug_resolution');
	$t_projection_id = isset( $p_issue['projection'] ) ? mci_get_projection_id( $p_issue['projection'] ) : config_get('default_bug_resolution');
	$t_eta_id = isset( $p_issue['eta'] ) ? mci_get_eta_id( $p_issue['eta'] ) : config_get('default_bug_eta');
	$t_view_state_id = isset( $p_issue['view_state'] ) ?  mci_get_view_state_id( $p_issue['view_state'] ) : config_get( 'default_bug_view_status' );
	$t_reporter_id = isset( $p_issue['reporter'] ) ? mci_get_user_id( $p_issue['reporter'] )  : 0;
	$t_summary = $p_issue['summary'];
	$t_description = $p_issue['description'];
	$t_notes = isset( $p_issue['notes'] ) ? $p_issue['notes'] : array();

	if( $t_reporter_id == 0 ) {
		$t_reporter_id = $t_user_id;
	} else {
		if( $t_reporter_id != $t_user_id ) {

			# Make sure that active user has access level required to specify a different reporter.
			$t_specify_reporter_access_level = config_get( 'mc_specify_reporter_on_add_access_level_threshold' );
			if( !access_has_project_level( $t_specify_reporter_access_level, $t_project_id, $t_user_id ) ) {
				return mci_soap_fault_access_denied( $t_user_id, "Active user does not have access level required to specify a different issue reporter" );
			}
		}
	}

	if(( $t_project_id == 0 ) || !project_exists( $t_project_id ) ) {
		if( $t_project_id == 0 ) {
			return new soap_fault( 'Client', '', "Project '" . $t_project['name'] . "' does not exist." );
		} else {
			return new soap_fault( 'Client', '', "Project with id '" . $t_project_id . "' does not exist." );
		}
	}

	if( !access_has_project_level( config_get( 'report_bug_threshold' ), $t_project_id, $t_user_id ) ) {
		return mci_soap_fault_access_denied( "User '$t_user_id' does not have access right to report issues" );
	}

	#if ( !access_has_project_level( config_get( 'report_bug_threshold' ), $t_project_id ) ||
	#	!access_has_project_level( config_get( 'report_bug_threshold' ), $t_project_id, $v_reporter ) ) {
	#	return new soap_fault( 'Client', '', "User does not have access right to report issues." );
	#}

	if(( $t_handler_id != 0 ) && !user_exists( $t_handler_id ) ) {
		return new soap_fault( 'Client', '', "User '$t_handler_id' does not exist." );
	}

	//$t_category = isset ( $p_issue['category'] ) ? $p_issue['category'] : null;

	$t_category = 'NIW';

	$t_category_id = translate_category_name_to_id( $t_category, $t_project_id );
	if ( $t_category_id == 0 && !config_get( 'allow_no_category' ) ) {
		if ( !isset( $p_issue['category'] ) || is_blank( $p_issue['category'] ) ) {
			return new soap_fault( 'Client', '', "Category field must be supplied." );
		} else {
			return new soap_fault( 'Client', '', "Category '" . $p_issue['category'] . "' not found for project '$t_project_id'." );
		}
	}

	if ( isset( $p_issue['version'] ) && !is_blank( $p_issue['version'] ) && !version_get_id( $p_issue['version'], $t_project_id ) ) {
		$t_version = $p_issue['version'];

		$t_error_when_version_not_found = config_get( 'mc_error_when_version_not_found' );
		if( $t_error_when_version_not_found == ON ) {
			$t_project_name = project_get_name( $t_project_id );
			return new soap_fault( 'Client', '', "Version '$t_version' does not exist in project '$t_project_name'." );
		} else {
			$t_version_when_not_found = config_get( 'mc_version_when_not_found' );
			$t_version = $t_version_when_not_found;
		}
	}

	if ( is_blank( $t_summary ) ) {
		return new soap_fault( 'Client', '', "Mandatory field 'summary' is missing." );
	}

	if ( is_blank( $t_description ) ) {
		return new soap_fault( 'Client', '', "Mandatory field 'description' is missing." );
	}

	$t_bug_data = new BugData;
	$t_bug_data->profile_id = 0;
	$t_bug_data->project_id = $t_project_id;
	$t_bug_data->reporter_id = $t_reporter_id;
	$t_bug_data->handler_id = $t_handler_id;
	$t_bug_data->priority = $t_priority_id;
	$t_bug_data->severity = $t_severity_id;
	$t_bug_data->reproducibility = $t_reproducibility_id;
	$t_bug_data->status = $t_status_id;
	$t_bug_data->resolution = $t_resolution_id;
	$t_bug_data->projection = $t_projection_id;
	$t_bug_data->category_id = $t_category_id;
	$t_bug_data->date_submitted = isset( $p_issue['date_submitted'] ) ? $p_issue['date_submitted'] : '';
	$t_bug_data->last_updated = isset( $p_issue['last_updated'] ) ? $p_issue['last_updated'] : '';
	$t_bug_data->eta = $t_eta_id;
	$t_bug_data->profile_id = isset ( $p_issue['profile_id'] ) ? $p_issue['profile_id'] : 0;
	$t_bug_data->os = isset( $p_issue['os'] ) ? $p_issue['os'] : '';
	$t_bug_data->os_build = isset( $p_issue['os_build'] ) ? $p_issue['os_build'] : '';
	$t_bug_data->platform = isset( $p_issue['platform'] ) ? $p_issue['platform'] : '';
	$t_bug_data->version = isset( $p_issue['version'] ) ? $p_issue['version'] : '';
	$t_bug_data->fixed_in_version = isset( $p_issue['fixed_in_version'] ) ? $p_issue['fixed_in_version'] : '';
	$t_bug_data->build = isset( $p_issue['build'] ) ? $p_issue['build'] : '';
	$t_bug_data->view_state = $t_view_state_id;
	$t_bug_data->summary = $t_summary;
	$t_bug_data->sponsorship_total = isset( $p_issue['sponsorship_total'] ) ? $p_issue['sponsorship_total'] : 0;
	if (  isset ( $p_issue['sticky']) &&
	     access_has_project_level( config_get( 'set_bug_sticky_threshold', null, null, $t_project_id ), $t_project_id ) ) {
	    $t_bug_data->sticky = $p_issue['sticky'];
	}

	if ( isset( $p_issue['due_date'] ) && access_has_global_level( config_get( 'due_date_update_threshold' ) ) ) {
		$t_bug_data->due_date = mci_iso8601_to_timestamp( $p_issue['due_date'] );
	} else {
		$t_bug_data->due_date = date_get_null();
	}

	if( access_has_project_level( config_get( 'roadmap_update_threshold' ), $t_bug_data->project_id, $t_user_id ) ) {
		$t_bug_data->target_version = isset( $p_issue['target_version'] ) ? $p_issue['target_version'] : '';
	}

	# omitted:
	# var $bug_text_id
	# $t_bug_data->profile_id;
	# extended info
	$t_bug_data->description = $t_description;
	$t_bug_data->steps_to_reproduce = isset( $p_issue['steps_to_reproduce'] ) ? $p_issue['steps_to_reproduce'] : '';
	$t_bug_data->additional_information = isset( $p_issue['additional_information'] ) ? $p_issue['additional_information'] : '';

	# submit the issue
	$t_issue_id = $t_bug_data->create();

	/*$p_issue['custom_fields'] = array(
		array('field' => array('id' => '25'), 'value' => $p_issue['niw_number']),//niw_no
		array('field' => array('id' => '8'), 'value' => $p_issue['equipment_type']),//subsystem
		array('field' => array('id' => '13'), 'value' => $p_issue['outage_plan']),//outageplan
		array('field' => array('id' => '7'), 'value' => $p_issue['branch']),//branch
		array('field' => array('id' => '9'), 'value' => $p_issue['cellname']),//cellname
		array('field' => array('id' => '15'), 'value' => $p_issue['start_date']),//start_date
		array('field' => array('id' => '14'), 'value' => $p_issue['end_date']),//end_date
		array('field' => array('id' => '10'), 'value' => $p_issue['ne_region']),//ne_region
		array('field' => array('id' => '6'), 'value' => $p_issue['site_name']),//site_name
		array('field' => array('id' => '24'), 'value' => md5($p_issue['niw_number'].$t_summary.$p_issue['equipment_type'].$p_issue['outage_plan'].$t_description))
	);*/

	foreach( $p_issue['custom_fields'] as $t_custom_field ) {
		if($t_custom_field['field']['id'] == '25'){
			$niw_number_value = $t_custom_field['value'];
		}
		if($t_custom_field['field']['id'] == '8'){
			$niw_subsytem_value = $t_custom_field['value'];
		}
		if($t_custom_field['field']['id'] == '13'){
			$niw_outageplan_value = $t_custom_field['value'];
		}
	}

	if(is_array($p_issue['custom_fields'])){
		$p_issue['custom_fields'][] = array('field' => array('id' => '24'), 'value' => md5($niw_number_value.$t_summary.$niw_subsytem_value.$niw_outageplan_value.$t_description));
	}

	mci_issue_set_custom_fields( $t_issue_id, $p_issue['custom_fields'], false );
	if ( isset ( $p_issue['monitors'] ) )
	    mci_issue_set_monitors( $t_issue_id , $t_user_id, $p_issue['monitors'] );

	if( isset( $t_notes ) && is_array( $t_notes ) ) {
		foreach( $t_notes as $t_note ) {
			if( isset( $t_note['view_state'] ) ) {
				$t_view_state = $t_note['view_state'];
			} else {
				$t_view_state = config_get( 'default_bugnote_view_status' );
			}

			$note_type = isset ( $t_note['note_type'] ) ? (int) $t_note['note_type'] : BUGNOTE;
			$note_attr = isset ( $t_note['note_type'] ) ? $t_note['note_attr'] : '';

			$t_view_state_id = mci_get_enum_id_from_objectref( 'view_state', $t_view_state );
			bugnote_add( $t_issue_id, $t_note['text'], mci_get_time_tracking_from_note( $t_issue_id, $t_note ), $t_view_state_id == VS_PRIVATE, $note_type, $note_attr, $t_user_id, FALSE );
		}
	}

	if ( isset ( $p_issue['tags']) && is_array ( $p_issue['tags']) ) {
		mci_tag_set_for_issue( $t_issue_id, $p_issue['tags'], $t_user_id );
	}

	email_new_bug( $t_issue_id );

	return $t_issue_id;
}


// * Function untuk mengubah STATUS pada NIW sesuai id eksternal BY IRFAN
function mc_niw_update( $p_username, $p_password, $p_issue ) {
	// ** Cari ID Eksternal di dalam database
	$queryField = "SELECT id FROM mantis_custom_field_table
						WHERE name = 'id_external' ";
	$fieldResult = db_query_bound($queryField);
	while( $bugField = db_fetch_array( $fieldResult ) ) {
		$field[] = $bugField;
	}

	// ** Query utk cari data sesuai id_external dan value = md5
	$query = "SELECT * FROM mantis_custom_field_string_table
					WHERE field_id = '".$field[0]['id']."' AND value = '".$p_issue['custom_fields'][3]['value']."' ";
	$t_result = db_query_bound( $query);

	while( $t_bug_row = db_fetch_array( $t_result ) ) {
		$bug[] = $t_bug_row;
	}

	// ** Update Query status sesuai bug id
	if($p_issue['status']['name'] == 'closed'){
			$statusID = 90;
		}
	if($p_issue['status']['name'] == 'new'){
			$statusID = 10;
		}
	foreach($bug as $bugKey => $bugValue){
		$queryUpdate = "UPDATE mantis_bug_table SET status = '".$statusID."'
							WHERE id = '".$bug[$bugKey]['bug_id']."'
						";

		$result2 = db_query_bound( $queryUpdate);
		$row2 = db_result( $result2 );
	}
	return $row2;

}

function ran_get_coin_by_id($coinKey)
{
    $sql = "SELECT * FROM ".COIN_TABLE." WHERE id = '".$coinKey."' LIMIT 1";
    $rs = db_query_bound($sql);
    return db_fetch_array($rs);
}

// * GET ISSUE FROM PROJECTS BY WAHYU
function mc_issue_get_detail_from_project( $p_username, $p_password, $p_project_name, $p_issue_id ) {
	$final_bug = '';
	$final_bug2 = '';
	$arr_id = array();

	// ** QUERY TO GET PROJECT DATA WITH PARAMETER POST PROJECT NAME
    // one issue can
    //  - get many files, many revisions
    //  - refer to project, category, user
    //  - extend (1-to-1) to bug_text table
    //  - refer to coin and temip

    $queryProject = "SELECT
            a.id as project_id, a.name as project_name,
            c.name as category_name,
            d.*,
            b.*,
            e.username,
            e.email as email_creator,
            f.filename
						FROM (mantis_project_table a LEFT JOIN mantis_bug_table b ON b.project_id = a.id)
        LEFT JOIN mantis_category_table c ON c.id = b.category_id
									LEFT JOIN mantis_bug_text_table d ON d.id = b.id
										LEFT JOIN mantis_user_table e ON e.id = b.reporter_id
        LEFT JOIN mantis_bug_file_table f ON b.id = f.bug_id
        WHERE b.id = '$p_issue_id'
        ORDER BY f.id";
	$projectResult = db_query_bound($queryProject);

	while($p_bug_project = db_fetch_array ( $projectResult)){
		$project[] = $p_bug_project;
		//$arr_id[] = $p_bug_project['id'];
        //
		// ** GET TEMP_ID FROM OPTIONAL 2
        if (!empty($p_bug_project['optional2']))
            $temip_id = $p_bug_project['optional2'];
        if (!empty($p_bug_project['optional3']))
            $coin_id  = $p_bug_project['optional3'];
	}
    $bug_id = $p_issue_id;

	// ** QUERY TO FIND OTHER CUSTOM FIELD DATA
	$customQuery = "SELECT a.*,b.name
							FROM mantis_custom_field_string_table a LEFT JOIN mantis_custom_field_table b
								ON b.id = a.field_id
        WHERE a.bug_id IN (".$bug_id.") ";

	$customResult = db_query_bound($customQuery);
	while($p_bug_custom = db_fetch_array ( $customResult)){
		//$custom_bug[] = $p_bug_custom;
        foreach($project as $projectKey2 => $projectValue2){
			if($projectValue2['id'] == $p_bug_custom['bug_id']){
					$project[$projectKey2][$p_bug_custom['name']] = $p_bug_custom['value'];
if ($p_bug_custom['name'] == 'optional3') $coin_id = $p_bug_custom['value'];
			}
        }
	}
	// ** ENTER OTHER CUSTOM FIELD DATA TO PROJECT ARRAY
    /*
	foreach($project as $projectKey2 => $projectValue2){
		foreach($custom_bug as $customValue){
			if($projectValue2['id'] == $customValue['bug_id']){
					$project[$projectKey2][$customValue['name']] = $customValue['value'];
			}
		}
		// ** GET TEMP_ID FROM OPTIONAL 2
		$temip_id[] = !empty($project[$projectKey2]['optional2']) ? $project[$projectKey2]['optional2']:"";
		$coin_id[] = !empty($project[$projectKey2]['optional3']) ? $project[$projectKey2]['optional3'] : "";
	}
     */

	// ** QUERY TO FIND TABLE TEMIP
    /*
	if(is_array($temip_id)){
		foreach($temip_id as $temipKey => $temipValue){
			$finalQuery = "SELECT * from temip
								WHERE temip_id = '".$temip_id[$temipKey]."'
							";

			$finalResult = db_query_bound($finalQuery);
			while($p_bug_final = db_fetch_array ( $finalResult)){
				$final_bug[] = $p_bug_final;
			}
		}
	}
     */
	$now = date('Ymd');

	// ** QUERY TO FIND TABLE COIN
    /*
	if(is_array($coin_id)){
		foreach($coin_id as $coinKey => $coinValue){
            if ($p_bug_final2 = ran_get_coin_by_id($coinValue))
            {
				$final_bug2[] = $p_bug_final2;
			}
		}
	}
     */

	// ** ENTER TEMIP & COIN DATA TO PROJECT ARRAY
	foreach($project as $projectKey3 => $projectValue3){
		if(is_array($final_bug)){
			foreach($final_bug as $finalKey => $finalValue){
				if($projectValue3['optional2'] == $finalValue['Temip_id']){
					$project[$projectKey3]['Specific_problem'] = $finalValue['Specific_problem'];
					$project[$projectKey3]['Managed_object'] = $finalValue['Managed_object'];
					$project[$projectKey3]['site_name_temip'] = $finalValue['Site_name'];
					$project[$projectKey3]['Notification_id'] = $finalValue['Notification_id'];
					$project[$projectKey3]['Probable_cause'] = $finalValue['Probable_cause'];
				}
			}
		}
        if ($p_bug_final2 = ran_get_coin_by_id($coin_id))
        //if(is_array($final_bug2))
        {
            //foreach($final_bug2 as $finalKey2 => $finalValue2){
                if($projectValue3['optional3'] == $p_bug_final2['id']){
                    $project[$projectKey3]['ne_region']            = $p_bug_final2['ne_region'];
                    $project[$projectKey3]['branch']               = $p_bug_final2['branch'];
                    $project[$projectKey3]['area']                 = $p_bug_final2['area'];
                    $project[$projectKey3]['sales_area']           = $p_bug_final2['sales_area'];
                    $project[$projectKey3]['cluster_area']         = $p_bug_final2['cluster_area'];
                    $project[$projectKey3]['pic_cluster']          = $p_bug_final2['pic_cluster'];
                    $project[$projectKey3]['nik']                  = $p_bug_final2['nik'];
                    $project[$projectKey3]['email']                = $p_bug_final2['email'];
                    $project[$projectKey3]['bts_name']             = $p_bug_final2['bts_name'];
                    $project[$projectKey3]['bts_type']             = $p_bug_final2['bts_type'];
                    $project[$projectKey3]['bts_integration_date'] = $p_bug_final2['bts_integration_date'];
                    $project[$projectKey3]['bts_code']             = $p_bug_final2['bts_code'];
                    $project[$projectKey3]['coverage_type']        = $p_bug_final2['coverage_type'];
                    $project[$projectKey3]['band']                 = $p_bug_final2['band'];
                    $project[$projectKey3]['ne_id']                = $p_bug_final2['ne_id'];
                    $project[$projectKey3]['site_name']            = $p_bug_final2['site_name'];
                    $project[$projectKey3]['longitude']            = $p_bug_final2['longitude'];
                    $project[$projectKey3]['latitude']             = $p_bug_final2['latitude'];
                    $project[$projectKey3]['address']              = $p_bug_final2['address'];
                    $project[$projectKey3]['site_id']              = $p_bug_final2['site_id'];
                    $project[$projectKey3]['bsc_name']             = $p_bug_final2['bsc_name'];
                    $project[$projectKey3]['bsc_type']             = $p_bug_final2['bsc_type'];
                    $project[$projectKey3]['bsc_code']             = $p_bug_final2['bsc_code'];
                    $project[$projectKey3]['cellname']             = $p_bug_final2['cellname'];
                    $project[$projectKey3]['class']                = $p_bug_final2['class'];
                    $project[$projectKey3]['antena_type']          = $p_bug_final2['antena_type'];
                    $project[$projectKey3]['antena_height']        = $p_bug_final2['antena_height'];
                    $project[$projectKey3]['cell_coverage']        = $p_bug_final2['cell_coverage'];
                    $project[$projectKey3]['cell_code']            = $p_bug_final2['cell_code'];
                    $project[$projectKey3]['lac']                  = $p_bug_final2['lac'];
                    $project[$projectKey3]['ci']                   = $p_bug_final2['ci'];
                }
            //}
		}
	}

	return serialize($project);
}

// * GET ISSUE FROM PROJECTS BY IRFAN
function mc_issue_get_from_projects( $p_username, $p_password, $p_project_name ) {

	$final_bug = '';
	$final_bug2 = '';

	// ** QUERY TO GET PROJECT DATA WITH PARAMETER POST PROJECT NAME
	$queryProject = "SELECT a.id AS project_id, a.name AS project_name, c.name AS category_name, d.description, b . * , e.destination_bug_id as rel_id
							FROM (mantis_project_table a LEFT JOIN mantis_bug_table b ON b.project_id = a.id)
									LEFT JOIN mantis_category_table c ON c.id = a.id
										LEFT JOIN mantis_bug_text_table d ON d.id = b.id
											LEFT JOIN mantis_bug_relationship_table e ON e.source_bug_id = b.id
												WHERE a.name = '".$p_project_name."' AND b.status < 90
					";
	$projectResult = db_query_bound($queryProject);
	while($p_bug_project = db_fetch_array ( $projectResult)){
		$project[] = $p_bug_project;
		$arr_id[] = $p_bug_project['id'];
	}

	$bug_id = implode(",",$arr_id);

	// ** QUERY TO FIND OTHER CUSTOM FIELD DATA
	$customQuery = "SELECT a.*,b.name
							FROM mantis_custom_field_string_table a LEFT JOIN mantis_custom_field_table b
								ON b.id = a.field_id
									WHERE a.bug_id in(".$bug_id.")
						";

	$customResult = db_query_bound($customQuery);
	while($p_bug_custom = db_fetch_array ( $customResult)){
		$custom_bug[] = $p_bug_custom;
	}
	// ** ENTER OTHER CUSTOM FIELD DATA TO PROJECT ARRAY
	foreach($project as $projectKey2 => $projectValue2){
		foreach($custom_bug as $customKey => $customValue){
			if($projectValue2['id'] == $customValue['bug_id']){
					$project[$projectKey2][$customValue['name']] = $customValue['value'];
			}
		}
		// ** GET TEMP_ID FROM OPTIONAL 2
		$temip_id[] = !empty($project[$projectKey2]['optional2']) ? $project[$projectKey2]['optional2']:"";
		$coin_id[] = !empty($project[$projectKey2]['optional3']) ? $project[$projectKey2]['optional3'] : "";
	}

	// ** QUERY TO FIND TABLE TEMIP
	if(is_array($temip_id)){
		foreach($temip_id as $temipKey => $temipValue){
			$finalQuery = "SELECT * from temip
							WHERE temip_id = '".$temip_id[$temipKey]."'
							";

			$finalResult = db_query_bound($finalQuery);
			while($p_bug_final = db_fetch_array ( $finalResult)){
				$final_bug[] = $p_bug_final;
			}
		}
	}
	// ** QUERY TO FIND TABLE COIN
	if(is_array($coin_id)){
		foreach($coin_id as $coinKey => $coinValue){
			$finalQuery2 = "SELECT * from ".COIN_TABLE."
							WHERE id = '".$coin_id[$coinKey]."'
							";

			$finalResult2 = db_query_bound($finalQuery2);
			while($p_bug_final2 = db_fetch_array ( $finalResult2)){
				$final_bug2[] = $p_bug_final2;
			}
		}
	}
	// ** ENTER TEMIP & COIN DATA TO PROJECT ARRAY
	foreach($project as $projectKey3 => $projectValue3){
		if(is_array($final_bug)){
			foreach($final_bug as $finalKey => $finalValue){
				if($projectValue3['optional2'] == $finalValue['Temip_id']){
					$project[$projectKey3]['Specific_problem'] = $finalValue['Specific_problem'];
					$project[$projectKey3]['Managed_object'] = $finalValue['Managed_object'];
					$project[$projectKey3]['Site_name'] = $finalValue['Site_name'];
					$project[$projectKey3]['Notification_id'] = $finalValue['Notification_id'];
					$project[$projectKey3]['Probable_cause'] = $finalValue['Probable_cause'];
				}

			}
		}
		if(is_array($final_bug2)){
			foreach($final_bug2 as $finalKey2 => $finalValue2){
				if($projectValue3['optional3'] == $finalValue2['id']){
					$project[$projectKey3]['kecamatan'] = $finalValue2['Coin1'];
					$project[$projectKey3]['kelurahan'] = $finalValue2['Coin2'];
					$project[$projectKey3]['address'] = $finalValue2['Coin3'];
					$project[$projectKey3]['area'] = $finalValue2['Coin4'];
					$project[$projectKey3]['Coin5'] = $finalValue2['Coin5'];
					$project[$projectKey3]['Branch'] = $finalValue2['Coin6'];
					$project[$projectKey3]['Subsystem'] = $finalValue2['Coin7'];
					$project[$projectKey3]['Cellname'] = $finalValue2['Coin8'];
					$project[$projectKey3]['Ne_region'] = $finalValue2['Coin9'];
				}
			}
		}
	}

	return serialize($project);
}

// * GET FILTER ISSUE FROM PROJECTS BY IRFAN
function mc_issue_get_filter_from_project( $p_username, $p_password, $p_project_name, $filterData )
{
	$where = array();

    // COIN DATA
    if(!empty($filterData['area'])){
        $where[] = " AND l.area LIKE '%".$filterData['area']."%'  ";
	}
	if(!empty($filterData['site_name'])){
        $where[] = " AND (
            l.site_name LIKE '%".$filterData['site_name']."%' OR
            l.bsc_name  LIKE '%".$filterData['site_name']."%' OR
            l.bts_name  LIKE '%".$filterData['site_name']."%'
        )  ";
	}
    if(!empty($filterData['ne_name'])){
        $where[] = " AND (
            l.site_name LIKE '%".$filterData['ne_name']."%' OR
            l.cellname  LIKE '%".$filterData['ne_name']."%' OR
            l.bts_name  LIKE '%".$filterData['ne_name']."%'
        )  ";
    }
    if(!empty($filterData['ci'])){
        $where[] = " AND (
            l.ci LIKE '%".$filterData['ci']."%'
        )  ";
    }
	if(!empty($filterData['address'])){
		$addExplode = explode(" ", $filterData['address']);
		foreach($addExplode as $addExplodeKey => $addExplodeValue){
			if($addExplodeValue != ""){
				$addExplode2[] = $addExplodeValue;
			}
		}
		$addImplode = implode(" ",$addExplode2);
		$where[] = " AND l.address LIKE '%".strtolower($addImplode)."%'  ";
	}
	if(!empty($filterData['ne_region'])){
		$where[] = " AND l.ne_region LIKE '%".$filterData['ne_region']."%'  ";
	}
    if(!empty($filterData['branch'])){
        $where[] = " AND l.branch LIKE '%".$filterData['branch']."%'  ";
    }
	// COIN DATA

	//TIME TRACKING
    if(!empty($filterData['from']))
    {
        $where[] = sprintf(
            " AND %s BETWEEN %d AND %d ",
            (empty($filterData['ds'])) ? 'n.value' : 'tte.value',
            mktime(
                0, 0, 0,
                substr($filterData['from'], 5,  2),
                substr($filterData['from'],    -2),
                substr($filterData['from'], 0,  4)
            ),
            mktime(
                23, 59, 59,
                substr($filterData['to'], 5,  2),
                substr($filterData['to'],    -2),
                substr($filterData['to'], 0,  4)
            )
        );
	}
	//TIME TRACKING

	// ** PAGINATION
    //$pagination = " LIMIT 0,30";
	if(!empty($filterData['paginate'])){
        $pagination = " LIMIT "
            . $filterData['paginate']['posisi'].","
            . $filterData['paginate']['batas'] ." ";
	}

	// ** Filter by Ticket number
	if(!empty($filterData['no_ticket'])){
		$no_ticket = intval($filterData['no_ticket']);
		$where[] = " AND b.id = ".$no_ticket." ";
	}

	if(!empty($filterData['alarm_no'])){
		$alarmNo = intval($filterData['alarm_no']);
		$where[] = " AND b.id = ".$alarmNo." ";
	}

	// ** Filter by STATUS ID
	$status = '';
    if(!empty($filterData['status']) && $filterData['status']!='all')
    {
        if ($filterData['status']=='notdone')
            $where[] = " AND b.status != 90 ";
        elseif (in_array($p_project_name, array('alarm', 'niw')))
        {
            switch ($filterData['status'])
            {
            case 'new':     $where[] = " AND b.status != 90 "; break;
            case 'closed':  $where[] = " AND b.status  = 90 "; break;
            }
        }
        elseif (in_array($p_project_name, array('cr', 'quality-tt'))) //quality-tt|cr
        {

	    if($p_project_name=='quality-tt'){
		$sql_related = "SELECT * 
				FROM  `mantis_bug_relationship_table` rel
				LEFT JOIN mantis_bug_table bug ON bug.id = rel.`destination_bug_id` 
				LEFT JOIN mantis_project_table project ON project.id = bug.project_id
				WHERE rel.`relationship_type` =2 AND project.id=1";
		$frelated = db_query_bound($sql_related);
		while($related_id = db_fetch_array ($frelated)){
			$rel_id[] = $related_id['destination_bug_id'];
		}
		if(is_array($rel_id))
			$where[] = " AND b.id NOT IN (".implode(",", $rel_id).") ";
	    }

            switch ($filterData['status'])
            {
            case 'new':       $where[] = " AND b.status NOT IN (90,80,70,30,75) "; break;
            case 'proposed':    $where[] = " AND b.status  = 30 "; break;
            case 'reject':    $where[] = " AND b.status  = 75 "; break;
            case 'closed':    $where[] = " AND b.status  = 90 "; break;
            case 'resolved':  $where[] = " AND b.status  = 80 "; break; /* propose-to-closed */
            case 'pending':   $where[] = " AND b.status  = 70 "; break;
            }
		
	}
        else // from Remedy: alarm-tt
        {
            switch ($filterData['status'])
            {
            case 'new':       $where[] = " AND b.status NOT IN (90,80,70) "; break;
            case 'closed':    $where[] = " AND b.status  = 90 "; break;
            case 'resolved':  $where[] = " AND b.status  = 80 "; break; /* propose-to-closed */
            case 'pending':   $where[] = " AND b.status  = 70 "; break;
            }
		}
	}

	// ** GET SEVERITY ID
	if(!empty($filterData['severity'])){
        switch ($filterData['severity'])
        {
        case 'new':       $where[] = " AND b.severity =  5 "; break;
        case 'feature':   $where[] = " AND b.severity = 10 "; break;
        case 'trivial':   $where[] = " AND b.severity = 20 "; break;
        case 'text':      $where[] = " AND b.severity = 30 "; break;
        case 'tweak':     $where[] = " AND b.severity = 40 "; break;
        case 'minor':     $where[] = " AND b.severity = 50 "; break;
        case 'major':     $where[] = " AND b.severity = 60 "; break;
        case 'crash':     $where[] = " AND b.severity = 70 "; break;
        case 'block':     $where[] = " AND b.severity = 80 "; break;
        case 'critical':  $where[] = " AND b.severity = 90 "; break;
        case 'p1':        $where[] = " AND b.severity = 41 "; break;
        case 'p2':        $where[] = " AND b.severity = 42 "; break;
        case 'p3':        $where[] = " AND b.severity = 43 "; break;
        }
	}

    if(!empty($filterData['indosat_id'])){
        $indosat_id = $filterData['indosat_id'];
        $where[] = " AND o.value = '".$indosat_id."' ";
    }

    if(!empty($filterData['description'])){
        $where[] = " AND d.description LIKE '%".$filterData['description']."%' ";
    }
	
	//tambahan fitrah
	if ($p_project_name=='cr') {
		if(!empty($filterData['cr_type'])){
			$where[] = " AND cr_type.value = '".$filterData['cr_type']."' ";
		} else {
				$where[] = " AND cr_type.value is NULL ";
		}
	}
	else {
				$where[] = " AND cr_type.value is NULL ";
	}
	
	if(!empty($filterData['r_type'])){
        $where[] = " AND r_type.value LIKE '%".$filterData['r_type']."%' ";
    }
	
	if(!empty($filterData['n_type'])){
        $where[] = " AND n_type.value LIKE '%".$filterData['n_type']."%' ";
    }
	
	if(is_array($where)){
		$where = implode(" ",$where);
	}

	if(empty($where)){
		$where = " ";
	}

	if(!empty($fields)){
		$fields = ", ".implode(", ", $fields);
	}

	// ** QUERY TO FILTER
    $queryFilter = "SELECT
        a.name AS project_name,
        c.name AS category_name,
        d.description, d.steps_to_reproduce, d.additional_information,
        b.*,
        user_table.email as email_creator,
        n.value AS time_tracking_start,
        tte.value AS time_tracking_end,
        ttt.value AS time_tracking_total,
        opt1.value AS optional1,
        o.value AS optional2,
        f.value AS optional3,
        ws.value AS work_status,
        pt.value AS pending_total,
        l.*
        $fields, 
		cr_type.value as cr_type,
		r_type.value as r_type,
		n_type.value as n_type,
		sla.value as sla,
		b.id,
	    (SELECT count(*) as rel_count FROM `mantis_bug_relationship_table` WHERE `source_bug_id` = b.id OR `destination_bug_id` = b.id) as rel_count
        FROM (mantis_bug_table b LEFT JOIN mantis_project_table a ON a.id = b.project_id)
            LEFT JOIN mantis_category_table c ON c.id = b.category_id
            LEFT JOIN mantis_bug_text_table d ON d.id = b.id
            LEFT JOIN mantis_user_table user_table ON user_table.id = b.reporter_id
            LEFT JOIN mantis_custom_field_string_table n    ON    n.bug_id = b.id AND    n.field_id = 2
            LEFT JOIN mantis_custom_field_string_table tte  ON  tte.bug_id = b.id AND  tte.field_id = 3
            LEFT JOIN mantis_custom_field_string_table ttt  ON  ttt.bug_id = b.id AND  ttt.field_id = 4
            LEFT JOIN mantis_custom_field_string_table opt1 ON opt1.bug_id = b.id AND opt1.field_id = 27
            LEFT JOIN mantis_custom_field_string_table o    ON    o.bug_id = b.id AND    o.field_id = 28
            LEFT JOIN mantis_custom_field_string_table f    ON    f.bug_id = b.id AND    f.field_id = 29
            LEFT JOIN mantis_custom_field_string_table ws   ON   ws.bug_id = b.id AND   ws.field_id = 1
            LEFT JOIN mantis_custom_field_string_table pt   ON   pt.bug_id = b.id AND   pt.field_id = 37
			LEFT OUTER JOIN mantis_custom_field_string_table cr_type   ON   cr_type.bug_id = b.id AND   cr_type.field_id = 40
            LEFT OUTER JOIN mantis_custom_field_string_table r_type   ON   r_type.bug_id = b.id AND   r_type.field_id = 41
            LEFT OUTER JOIN mantis_custom_field_string_table n_type   ON   n_type.bug_id = b.id AND   n_type.field_id = 42
			LEFT OUTER JOIN mantis_custom_field_string_table sla   ON   sla.bug_id = b.id AND   sla.field_id = 43
            LEFT JOIN ".COIN_TABLE." l ON l.id = f.value
            $qry_add
        WHERE a.name = '".$p_project_name."' $where
        ORDER BY n.value DESC $pagination
	";
	error_log("\n" .$queryFilter, 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;
	error_log("\n" .print_r($filterData,1), 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;
	
	$filterResult = db_query_bound($queryFilter);
    while($p_bug_filter = db_fetch_array ($filterResult)){
        $project[] = $p_bug_filter;
    }

	$queryNumData = "
	SELECT COUNT(b.id) AS total
	FROM (mantis_bug_table b LEFT JOIN mantis_project_table a ON a.id = b.project_id)
            LEFT JOIN mantis_category_table c ON c.id = b.category_id
            LEFT JOIN mantis_bug_text_table d ON d.id = b.id
			LEFT JOIN mantis_custom_field_string_table n    ON    n.bug_id = b.id AND    n.field_id = 2
			LEFT JOIN mantis_custom_field_string_table tte  ON  tte.bug_id = b.id AND  tte.field_id = 3
			LEFT JOIN mantis_custom_field_string_table ttt  ON  ttt.bug_id = b.id AND  ttt.field_id = 4
			LEFT JOIN mantis_custom_field_string_table opt1 ON opt1.bug_id = b.id AND opt1.field_id = 27
			LEFT JOIN mantis_custom_field_string_table o    ON    o.bug_id = b.id AND    o.field_id = 28
            LEFT JOIN mantis_custom_field_string_table f    ON    f.bug_id = b.id AND    f.field_id = 29
			LEFT JOIN mantis_custom_field_string_table ws   ON   ws.bug_id = b.id AND   ws.field_id = 1
			LEFT JOIN mantis_custom_field_string_table pt   ON   pt.bug_id = b.id AND   pt.field_id = 37
			LEFT JOIN mantis_custom_field_string_table cr_type   ON   cr_type.bug_id = b.id AND   cr_type.field_id = 40
            LEFT JOIN mantis_custom_field_string_table r_type   ON   r_type.bug_id = b.id AND   r_type.field_id = 41
            LEFT JOIN mantis_custom_field_string_table n_type   ON   n_type.bug_id = b.id AND   n_type.field_id = 42
            LEFT JOIN ".COIN_TABLE." l ON l.id = f.value
            $qry_add
    WHERE a.name = '".$p_project_name."' $where
	";
	$numDataRes = db_query_bound($queryNumData);
	$project["jumlah_data"] = db_result($numDataRes);

	return serialize($project);
}

// ** PAGINATION COUNT
function mc_issue_get_count_projects( $p_username, $p_password, $p_project_name ,$filterData ) {

	// ** QUERY TO FIND TABLE COIN
	$where2 = array();
	$final_bug3 = '';
	$now = date('Ymd');

	if($p_project_name == 'alarm'){
			$qry_add = "left join mantis_custom_field_string_table h ON h.field_id = 28 and h.bug_id = b.id
														left join temip t on t.Temip_id = h.value ";
	}else{
			$qry_add = "left join mantis_custom_field_string_table h ON h.field_id = 28 and h.bug_id = b.id ";
	}

	if(!empty($filterData['area'])){
		$where2[] = " area like '%".$filterData['area']."%'  ";
	}
	if(!empty($filterData['site_name'])){
			$where2[] = " Site_name like '%".$filterData['site_name']."%'  ";
		}
	if(!empty($filterData['address'])){
		$addExplode = explode(" ", $filterData['address']);
		foreach($addExplode as $addExplodeKey => $addExplodeValue){
			if($addExplodeValue != ""){
				$addExplode2[] = $addExplodeValue;
			}
		}
		$addImplode = implode(" ",$addExplode2);
		$where2[] = " address like '%".strtolower($addImplode)."%'  ";
		//$address3 = true;
	}
	if(!empty($filterData['ne_region'])){
		$where2[] = " ne_region like '%".$filterData['ne_region']."%'  ";
		//$ne_region3 = true;
	}
	// ** GET STATUS ID
	$status = '';
	if(!empty($filterData['status'])){
		if( $p_project_name == 'alarm'  || $p_project_name == 'niw' ){
			if($filterData['status'] == 'new'){
				$statusTemip =  " AND b.status != '90' ";
			}
			else if($filterData['status'] == 'closed'){
				$status = 90;
			}
		}
	else if($p_project_name == 'alarm-tt' || $p_project_name == 'quality-tt' || $p_project_name == 'cr' ){
		if($filterData['status'] == 'new'){
			$statusRemedy = array(90,70,80);
		}
		else if($filterData['status'] == 'closed'){
			$status = 90;
		}
		else if($filterData['status'] == 'pending'){
			$status = 70;
		}
		// ** propose to closed by remedy
		else if($filterData['status'] == 'resolved'){
			$status = 80;
		}
	}

		if($filterData['status'] == 'notdone'){
			$where[] = " AND b.status != '90' ";
		}
		else{
			if(!empty($status)){
				$where[] = " AND b.status = ".$status." ";
			}

			// ** STATUS REMEDY
			$where11 = array();
			if(!empty($statusRemedy)){
				$where11[] = " b.status != ".$statusRemedy[0]." ";
				$where11[] = " b.status != ".$statusRemedy[1]." ";
				$where11[] = " b.status != ".$statusRemedy[2]." ";
			}
			if(is_array($where11)){
				$where11 = implode(" AND",$where11);
			}
			if(!empty($where11)){
				$where[] = "AND ( ".$where11." ) ";
			}
			if(empty($where11)){
				$where11 = " ";
			}
			// ** STATUS TEMIP
			if(!empty($statusTemip)){
				$where[] = $statusTemip;
			}
			if(empty($statusTemip)){
				$statusTemip = " ";
			}

		}
	}
	// * TIME TRACKING START
	// ** CHANGE TIME 1
	if(!empty($filterData['from'])){
		$date = substr($filterData['from'],-2);
		$month = substr($filterData['from'],5,2);
		$year = substr($filterData['from'],0,4);
		$merge_time_first = $year."-".$month."-".$date." 00:00:00";

		$date2 = substr($filterData['to'],-2);
		$month2 = substr($filterData['to'],5,2);
		$year2 = substr($filterData['to'],0,4);
		$merge_time_end = $year2."-".$month2."-".$date2." 23:59:59";

		$qry_add .= " left join mantis_custom_field_string_table i ON i.field_id = 2 and i.value BETWEEN UNIX_TIMESTAMP('".$merge_time_first."') AND UNIX_TIMESTAMP('".$merge_time_end."') ";

		$where[] = " and b.id = i.bug_id ";
	}

	// ** NO TICKET
	if(!empty($filterData['no_ticket'])){
		$no_ticket = intval($filterData['no_ticket']);
		$where[] = " AND b.id = ".$no_ticket." ";
	}
	if(!empty($filterData['ne_region'])){
			$where[] = " AND ne_region like '%".$filterData['ne_region']."%'  ";

		}



	// ** IF SEARCH FORM IS ACTIVE
	if( !empty($where2) && ($filterData['paginate'] && $filterData['status']) ){

		$finalQuery11 = "SELECT * from ".COIN_TABLE." WHERE ".implode(" AND ",$where2)." ";

		$finalResult11 = db_query_bound($finalQuery11);
		while($p_bug_final11 = db_fetch_array ( $finalResult11)){
			$final_bug11[] = $p_bug_final11;
			$arr_id11[] = $p_bug_final11['id'];
		}

		if(is_array($arr_id11))
			$coin_id11 = implode(",",$arr_id11);
		$qry_add .= "left join mantis_custom_field_string_table f ON f.field_id = 29 and f.value in ($coin_id11)";
		$where[] = " and b.id = f.bug_id ";
	}
		// ** GET SEVERITY ID
	$severity ='';
	if(!empty($filterData['severity'])){
		if($filterData['severity'] == 'minor'){
			$severity = 50;
					}
		else if($filterData['severity'] == 'text'){
			$severity = 30;
		}
		else if($filterData['severity'] == 'feature'){
			$severity = 10;
					}
		else if($filterData['severity'] == 'major'){
			$severity = 60;
		}
		else if($filterData['severity'] == 'trivial'){
			$severity = 20;
					}
		else if($filterData['severity'] == 'tweak'){
			$severity = 40;
		}
		else if($filterData['severity'] == 'crash'){
			$severity = 70;
					}
		else if($filterData['severity'] == 'block'){
			$severity = 80;
		}
		else if($filterData['severity'] == 'new'){
			$severity = 5;
					}
		else if($filterData['severity'] == 'critical'){
			$severity = 90;
		}
		$where[] = " AND b.severity = $severity ";
	}
	if(!empty($filterData['alarm_no'])){
		$alarmNo = intval($filterData['alarm_no']);
		$where[] = " AND b.id = ".$alarmNo." ";
	}

	if(is_array($where)){
		$where = implode(" ",$where);
	}

	if(empty($where)){
		$where = " ";
	}

			// ** QUERY TO FIND OTHER CUSTOM FIELD DATA
			$customQuery11 = "SELECT count(distinct b.id) as jumlah_halaman
								FROM (mantis_project_table a LEFT JOIN mantis_bug_table b ON b.project_id = a.id)
										LEFT JOIN mantis_category_table c ON c.id = a.id
											LEFT JOIN mantis_bug_text_table d ON d.id = b.id
												LEFT JOIN mantis_bug_relationship_table e ON e.source_bug_id = b.id
														$qry_add
																WHERE a.name = '$p_project_name'
																		and b.id = h.bug_id
																				$where
								";

			$customResult11 = db_query_bound($customQuery11);
			$row 	= db_result( $customResult11 );



	//}

	//
	return $row;
}


// ** GET ISSUE FROM PROJECTS (CLOSED) BY IRFAN
function mc_issue_get_from_closed_projects( $p_username, $p_password, $p_project_name ) {
	$final_bug = '';
	$final_bug2 = '';
	$queryProject = "SELECT a.id as project_id ,a.name as project_name,c.name as category_name,d.description,b.*
						FROM (mantis_project_table a LEFT JOIN mantis_bug_table b ON b.project_id = a.id)
								LEFT JOIN mantis_category_table c ON c.id = a.id
									LEFT JOIN mantis_bug_text_table d ON d.id = b.id
										WHERE a.name = '".$p_project_name."' AND b.status = 90
					";

	$projectResult = db_query_bound($queryProject);
	while($p_bug_project = db_fetch_array ( $projectResult)){
		$project[] = $p_bug_project;
		$arr_id[] = $p_bug_project['id'];
	}

	$bug_id = implode(",",$arr_id);

	// ** QUERY TO FIND OTHER CUSTOM FIELD DATA
	$customQuery = "SELECT a.*,b.name
							FROM mantis_custom_field_string_table a LEFT JOIN mantis_custom_field_table b
								ON b.id = a.field_id
									WHERE a.bug_id in(".$bug_id.")
						";

	$customResult = db_query_bound($customQuery);
	while($p_bug_custom = db_fetch_array ( $customResult)){
		$custom_bug[] = $p_bug_custom;
	}

	// ** ENTER OTHER CUSTOM FIELD DATA TO PROJECT ARRAY
	foreach($project as $projectKey2 => $projectValue2){
		foreach($custom_bug as $customKey => $customValue){
			if($projectValue2['id'] == $customValue['bug_id']){
					$project[$projectKey2][$customValue['name']] = $customValue['value'];
			}
		}
		// ** GET TEMP_ID FROM OPTIONAL 2
		$temip_id[] = !empty($project[$projectKey2]['optional2']) ? $project[$projectKey2]['optional2']:"";
		$coin_id[] = !empty($project[$projectKey2]['optional3']) ? $project[$projectKey2]['optional3'] : "";
	}

	// ** QUERY TO FIND TABLE TEMIP
	if(is_array($temip_id)){
		foreach($temip_id as $temipKey => $temipValue){
			$finalQuery = "SELECT * from temip
								WHERE temip_id = '".$temip_id[$temipKey]."'
							";

			$finalResult = db_query_bound($finalQuery);
			while($p_bug_final = db_fetch_array ( $finalResult)){
				$final_bug[] = $p_bug_final;
			}
		}
	}

	// ** QUERY TO FIND TABLE COIN
	if(is_array($coin_id)){
		foreach($coin_id as $coinKey => $coinValue){
			$finalQuery2 = "SELECT * from ".COIN_TABLE."
								WHERE id = '".$coin_id[$coinKey]."'
							";

			$finalResult2 = db_query_bound($finalQuery2);
			while($p_bug_final2 = db_fetch_array ( $finalResult2)){
				$final_bug2[] = $p_bug_final2;
			}
		}
	}

	// ** ENTER TEMIP & COIN DATA TO PROJECT ARRAY
	foreach($project as $projectKey3 => $projectValue3){
		if(is_array($final_bug)){
			foreach($final_bug as $finalKey => $finalValue){
				if($projectValue3['optional2'] == $finalValue['Temip_id']){
					$project[$projectKey3]['Specific_problem'] = $finalValue['Specific_problem'];
					$project[$projectKey3]['Managed_object'] = $finalValue['Managed_object'];
					$project[$projectKey3]['Site_name'] = $finalValue['Site_name'];
					$project[$projectKey3]['Notification_id'] = $finalValue['Notification_id'];
					$project[$projectKey3]['Probable_cause'] = $finalValue['Probable_cause'];
				}

			}
		}
		if(is_array($final_bug2)){
			foreach($final_bug2 as $finalKey2 => $finalValue2){
				if($projectValue3['optional3'] == $finalValue2['id']){
					$project[$projectKey3]['kecamatan'] = $finalValue2['Coin1'];
					$project[$projectKey3]['kelurahan'] = $finalValue2['Coin2'];
					$project[$projectKey3]['address'] = $finalValue2['Coin3'];
					$project[$projectKey3]['area'] = $finalValue2['Coin4'];
					$project[$projectKey3]['Coin5'] = $finalValue2['Coin5'];
					$project[$projectKey3]['Branch'] = $finalValue2['Coin6'];
					$project[$projectKey3]['Subsystem'] = $finalValue2['Coin7'];
					$project[$projectKey3]['Cellname'] = $finalValue2['Coin8'];
					$project[$projectKey3]['Ne_region'] = $finalValue2['Coin9'];


					//$project[$projectKey3]['kecamatan'] = $finalValue2['Coin1'];
					//$project[$projectKey3]['kelurahan'] = $finalValue2['Coin2'];
					/*$project[$projectKey3]['ne_region'] = $finalValue2['ne_region'];
					$project[$projectKey3]['branch'] = $finalValue2['branch'];
					$project[$projectKey3]['area'] = $finalValue2['area'];
					$project[$projectKey3]['sales_area'] = $finalValue2['sales_area'];
					$project[$projectKey3]['cluster_area'] = $finalValue2['cluster_area'];
					$project[$projectKey3]['pic_cluster'] = $finalValue2['pic_cluster'];
					$project[$projectKey3]['nik'] = $finalValue2['nik'];
					$project[$projectKey3]['email'] = $finalValue2['email'];
					$project[$projectKey3]['bts_name'] = $finalValue2['bts_name'];
					$project[$projectKey3]['bts_type'] = $finalValue2['bts_type'];
					$project[$projectKey3]['bts_integration_date'] = $finalValue2['bts_integration_date'];
					$project[$projectKey3]['bts_code'] = $finalValue2['bts_code'];
					$project[$projectKey3]['coverage_type'] = $finalValue2['coverage_type'];
					$project[$projectKey3]['band'] = $finalValue2['band'];
					$project[$projectKey3]['ne_id'] = $finalValue2['ne_id'];
					$project[$projectKey3]['site_name'] = $finalValue2['site_name'];
					$project[$projectKey3]['longitude'] = $finalValue2['longitude'];
					$project[$projectKey3]['latitude'] = $finalValue2['latitude'];
					$project[$projectKey3]['address'] = $finalValue2['address'];
					$project[$projectKey3]['site_id'] = $finalValue2['site_id'];
					$project[$projectKey3]['bsc_name'] = $finalValue2['bsc_name'];
					$project[$projectKey3]['bsc_type'] = $finalValue2['bsc_type'];
					$project[$projectKey3]['bsc_code'] = $finalValue2['bsc_code'];
					$project[$projectKey3]['cellname'] = $finalValue2['cellname'];
					$project[$projectKey3]['class'] = $finalValue2['class'];
					$project[$projectKey3]['antena_type'] = $finalValue2['antena_type'];
					$project[$projectKey3]['antena_height'] = $finalValue2['antena_height'];
					$project[$projectKey3]['cell_coverage'] = $finalValue2['cell_coverage'];
					$project[$projectKey3]['cell_code'] = $finalValue2['cell_code'];
					$project[$projectKey3]['lac'] = $finalValue2['lac'];
					$project[$projectKey3]['ci'] = $finalValue2['ci'];*/
				}
			}
		}
	}

	return serialize($project);
}

// * GET ISSUE DETAILS FROM CLOSED PROJECTS BY IRFAN
function mc_issue_get_detail_from_closed_project( $p_username, $p_password, $p_project_name, $p_issue_id ) {
	$final_bug = '';
	$final_bug2 = '';

	// ** QUERY TO GET PROJECT DATA WITH PARAMETER POST PROJECT NAME
	$queryProject = "SELECT a.id as project_id ,a.name as project_name,c.name as category_name,d.*,b.*,e.username,f.filename
						FROM (mantis_project_table a LEFT JOIN mantis_bug_table b ON b.project_id = a.id)
								LEFT JOIN mantis_category_table c ON c.id = a.id
									LEFT JOIN mantis_bug_text_table d ON d.id = b.id
										LEFT JOIN mantis_user_table e ON e.id = b.reporter_id
											LEFT JOIN mantis_bug_file_table f ON f.bug_id = b.id
												WHERE a.name = '".$p_project_name."' AND b.id = '".$p_issue_id."'
															AND b.status = 90
					";
	$projectResult = db_query_bound($queryProject);
	while($p_bug_project = db_fetch_array ( $projectResult)){
		$project[] = $p_bug_project;
		$arr_id[] = $p_bug_project['id'];
	}
	$bug_id = implode(",",$arr_id);

	// ** QUERY TO FIND OTHER CUSTOM FIELD DATA
	$customQuery = "SELECT a.*,b.name
							FROM mantis_custom_field_string_table a LEFT JOIN mantis_custom_field_table b
								ON b.id = a.field_id
									WHERE a.bug_id in(".$bug_id.")
						";

	$customResult = db_query_bound($customQuery);
	while($p_bug_custom = db_fetch_array ( $customResult)){
		$custom_bug[] = $p_bug_custom;
	}
	// ** ENTER OTHER CUSTOM FIELD DATA TO PROJECT ARRAY
	foreach($project as $projectKey2 => $projectValue2){
		foreach($custom_bug as $customKey => $customValue){
			if($projectValue2['id'] == $customValue['bug_id']){
					$project[$projectKey2][$customValue['name']] = $customValue['value'];
			}
		}
		// ** GET TEMP_ID FROM OPTIONAL 2
		$temip_id[] = !empty($project[$projectKey2]['optional2']) ? $project[$projectKey2]['optional2']:"";
		$coin_id[] = !empty($project[$projectKey2]['optional3']) ? $project[$projectKey2]['optional3'] : "";
	}

	// ** QUERY TO FIND TABLE TEMIP
	if(is_array($temip_id)){
		foreach($temip_id as $temipKey => $temipValue){
			$finalQuery = "SELECT * from temip
							WHERE temip_id = '".$temip_id[$temipKey]."'
							";

			$finalResult = db_query_bound($finalQuery);
			while($p_bug_final = db_fetch_array ( $finalResult)){
				$final_bug[] = $p_bug_final;
			}
		}
	}
	// ** QUERY TO FIND TABLE COIN
	if(is_array($coin_id)){
		foreach($coin_id as $coinKey => $coinValue){
			$finalQuery2 = "SELECT * from ".COIN_TABLE."
								WHERE id = '".$coin_id[$coinKey]."'
							";

			$finalResult2 = db_query_bound($finalQuery2);
			while($p_bug_final2 = db_fetch_array ( $finalResult2)){
				$final_bug2[] = $p_bug_final2;
			}
		}
	}
	// ** ENTER TEMIP & COIN DATA TO PROJECT ARRAY
	foreach($project as $projectKey3 => $projectValue3){
		if(is_array($final_bug)){
			foreach($final_bug as $finalKey => $finalValue){
				if($projectValue3['optional2'] == $finalValue['Temip_id']){
					$project[$projectKey3]['Specific_problem'] = $finalValue['Specific_problem'];
					$project[$projectKey3]['Managed_object'] = $finalValue['Managed_object'];
					$project[$projectKey3]['Site_name'] = $finalValue['Site_name'];
					$project[$projectKey3]['Notification_id'] = $finalValue['Notification_id'];
					$project[$projectKey3]['Probable_cause'] = $finalValue['Probable_cause'];
				}

			}
		}
		if(is_array($final_bug2)){
			foreach($final_bug2 as $finalKey2 => $finalValue2){
				if($projectValue3['optional3'] == $finalValue2['id']){
					$project[$projectKey3]['kecamatan'] = $finalValue2['Coin1'];
					$project[$projectKey3]['kelurahan'] = $finalValue2['Coin2'];
					$project[$projectKey3]['address'] = $finalValue2['Coin3'];
					$project[$projectKey3]['area'] = $finalValue2['Coin4'];
					$project[$projectKey3]['Coin5'] = $finalValue2['Coin5'];
					$project[$projectKey3]['Branch'] = $finalValue2['Coin6'];
					$project[$projectKey3]['Subsystem'] = $finalValue2['Coin7'];
					$project[$projectKey3]['Cellname'] = $finalValue2['Coin8'];
					$project[$projectKey3]['Ne_region'] = $finalValue2['Coin9'];
				}
			}
		}
	}

	return serialize($project);
}

/*
// * CREATE ISSUE COIN AND GET ID BY IRFAN
function mc_issue_coin_add_get_id( $p_username, $p_password, $coinData ) {
	// ** QUERY TO INSERT COIN TABLE
	$queryAdd = "INSERT INTO coin(Coin5,Coin6,Coin7,Coin8,Coin9)
						VALUES(	'".$coinData['site_name']."',
								'".$coinData['branch']."',
								'".$coinData['subsystem']."',
								'".$coinData['cellname']."',
								'".$coinData['ne_region']."'
						)
				";
	$result = db_query_bound($queryAdd);
	$row 	= db_result( $result );

	// ** GET ID WHEN INSERT DATABASE
	$coinId 	= mysql_insert_id();

	return $coinId;
}
*/
/*
Function To Get Request ID From request ID
---wahyu---
*/
function mc_issue_get_issue_id_from_request_id( $p_username, $p_password, $request_id ) {


	if(/*!empty($p_username) && !empty($p_password) &&*/ !empty($request_id)){

		$query = "SELECT bug_id
				FROM  `mantis_custom_field_string_table` custom
				LEFT JOIN mantis_bug_table bug ON bug.id = custom.bug_id
				LEFT JOIN mantis_project_table project ON project.id = bug.project_id
				WHERE  `field_id` =  '28'
				AND value =  '$request_id'
				-- AND project.name !=  'ALARM'
";
		$result = db_query_bound($query);
		$row 	= db_result( $result );

		return $row;
	}else{
		return "0";
	}

	return $row;
}
/*
Function To Get Request ID From request ID
---wahyu---
*/
function mc_issue_get_issue_id_temip( $p_username, $p_password, $temip_data ) {


	if(!empty($p_username) && !empty($p_password) && !empty($request_id)){

		$query = "SELECT Temip_id
					FROM  `temip`
					WHERE (
					`Specific_problem` =  '".$temip_data['Specific_problem']."'
					AND  `Managed_object` =  '".$temip_data['Managed_object']."'
					AND  `Site_name` =  '".$temip_data['Site_name']."'
					AND  `Probable_cause` =  '".$temip_data['Probable_cause']."'
					)
					OR (
					`Managed_object` =  '".$temip_data['Managed_object']."'
					AND  `Notification_id` =  '".$temip_data['Notification_id']."'
					)";
		$result = db_query_bound($query);
		$temip_id = db_result( $result );

		$query = "SELECT bug_id
				FROM  `mantis_custom_field_string_table` custom
				LEFT JOIN mantis_bug_table bug ON bug.id = custom.bug_id
				LEFT JOIN mantis_project_table project ON project.id = bug.project_id
				WHERE  `field_id` =  '28'
				AND value =  '$temip_id'
				AND project.name = 'ALARM'";

		$result = db_query_bound($query);
		$bug_id = db_result( $result );



		return $bug_id;
	}else{
		return "0";
	}

	return $row;
}

function mc_issue_temip($p_username, $p_password, $p_issue){
	$bug_id = mc_issue_get_issue_id_temip($p_issue['additional']);

	if($bug_id>0)
		$result = mc_issue_update( $p_username, $p_password, $p_issue['data'] );
	else
		$result = mc_issue_add( $p_username, $p_password, $p_issue['data'] );

	return $result;

}

function mc_issue_get_niw_parent_id( $p_username, $p_password, $niw_number ) {
	// ** QUERY TO GET BUG ID

	if(!empty($niw_number)){
	        $queryAdd = "SELECT min( bug_id ) as bug_id
                                FROM `mantis_custom_field_string_table`
                                WHERE field_id = 30 AND value = '$niw_number'";
	        $result = db_query_bound($queryAdd);
	        $row 	= db_result( $result );
	}else{
	        $row = 0;
	}

	return $row;
}

function mc_issue_get_coin_id( $p_username, $p_password, $field ) {
	// ** QUERY TO INSERT COIN TABLE

	$where = '';

	if(is_array($field['name'])){
		for($i=0; $i < count($field['name']) ; $i++){
			$where[] = $field['name'][$i]." = '".$field['value'][$i]."' ";
		}
		if(is_array($where)){
			if($field['type']=='or')
				$where = " WHERE ".implode(" OR ", $where);
			else
				$where = " WHERE ".implode(" AND ", $where);
		}


	}else
		$where = " where lower(".$field['name'].") = '".strtolower($field['value'])."'";

	$queryAdd = "SELECT id FROM ".COIN_TABLE." $where";
	$result = db_query_bound($queryAdd);
	$row 	= db_result( $result );

	// ** GET ID WHEN INSERT DATABASE
	return $row;
}

function mc_issue_get_bugid( $p_username, $p_password, $field ) {
	// ** QUERY TO GET BUG ID

	$where = " WHERE field_id = '".$field['id']."' AND value = '". $field['value']."'";

	if(!empty($field)){
	        $queryAdd = "SELECT bug_id
                                FROM `mantis_custom_field_string_table`
                                $where";
	        $result = db_query_bound($queryAdd);
	        $row 	= db_result( $result );
	}else{
	        $row = 0;
	}

	return $row;
}

function mc_get_coin_data($user, $password, $id){

	$query = "SELECT * FROM ".COIN_TABLE." WHERE id = '$id'";
	
	$result = db_query_bound($query);

	$coin = db_fetch_array ( $result);
	return serialize($coin);
}

function mc_create_user($username, $password, $data_user){
	if(is_array($data_user)){
	        $queryAdd = "INSERT INTO mantis_user_table
			     values ('', '".$data_user['username']."', '".$data_user['name']."', '".$data_user['email']."',
					'".$data_user['pwd']."', 1, 0, 70,
					0,0, 0,
					'".$data_user['password']."', ".time().", ".time().")";
	        $result = db_query_bound($queryAdd);
	        $row 	= db_result( $result );

		//error_log("\n wahyu log " .' result ' . $result, 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;
		return $result;
	}

}

// ** CREATE USER FROM USER MANAGEMENT IN PORTAL BY IRFAN
function mc_create_user_by_ticket($username, $password, $data_user){
	if(is_array($data_user)){
		if(!empty($data_user['notification'])){
			$queryAdd = "INSERT INTO mantis_user_table
							 values ('', '".$data_user['username']."', '".$data_user['realname']."', '".$data_user['email']."',
								'".$data_user['password']."','".$data_user['enabled']."', 0, '70',
								0,0, 0,'".$data_user['cookie_string']."', ".time().", ".time().")";

			$notive = unserialize($data_user['notification']);

			$result = db_query_bound($queryAdd);
			$row 	= db_result( $result );
			$id = mysql_insert_id();
			

			foreach($notive as $nt){
				$queryEdit2 = "insert into executive_users(user_id,status,project_id) values($id,1,$nt)";

				$result23 =  db_query_bound($queryEdit2);
				$row24 = db_result($result23);
			}
			
			error_log("\n wahyu log 123" .' result ' .$notive, 3, '/home/wahyu/test.cpl') ;
			/*
			foreach($data_user['aco_result'] as $dataKey => $dataValue){
				if($dataValue == 'niws'){
					$project_id = 5;
				}
				else if($dataValue == 'changerequests'){
					$project_id = 4;
				}
				else if($dataValue == 'alerts'){
					$project_id = 2;
				}
				else if($dataValue == 'qualities'){
					$project_id = 1;
				}
				else if($dataValue == 'alarms'){
					$project_id = 3;
				}


				$queryEdit2 = "insert into executive_users(user_id,status,project_id) values($id,1,$project_id)";

				$result23 =  db_query_bound($queryEdit2);
				$row24 = db_result($result23);
				
			}
			*/
		}
		/*
		else if($data_user['notification'] == 'n'){
				$queryAdd = "INSERT INTO mantis_user_table
					 values ('', '".$data_user['username']."', '".$data_user['realname']."', '".$data_user['email']."',
						'".$data_user['password']."','".$data_user['enabled']."', 0, '70',
						0,0, 0,'".$data_user['cookie_string']."', ".time().", ".time().")";

				$result = db_query_bound($queryAdd);
				$row 	= db_result( $result );
				#error_log("\n wahyu log " .' result ' . $result, 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;
				$id = mysql_insert_id();
				#error_log("\n wahyu log " .' result ' . $id, 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;

			if($data_user['action'] == 'admin_edit'){

				$queryFind2 = "select id from mantis_user_table where username = .".$data_user['user_lawas']."  ";

				$result7 =  db_query_bound($queryFind2);
				$userId2 = db_result($result7);

				$queryChange = "UPDATE mantis_user_table
								SET username= '".$data_user['username']."', realname='".$data_user['realname']."',email = '".$data_user['email']."', last_visit = ".time()."
								WHERE id = $userId2
							";

				$result11 = db_query_bound($queryChange);
				$row11 	= db_result( $result11 );

				$queryDelete = "DELETE from executive_users where user_id = $userId2 ";

				$resultDelete =  db_query_bound($queryDelete);
				$row9 = db_result($resultDelete);
			}

		}
		*/
		else{
			$queryAdd = "INSERT INTO mantis_user_table
					 values ('', '".$data_user['username']."', '".$data_user['realname']."', '".$data_user['email']."',
						'".$data_user['password']."','".$data_user['enabled']."', 0, '70',
						0,0, 0,'".$data_user['cookie_string']."', ".time().", ".time().")";

			$result = db_query_bound($queryAdd);
			$row 	= db_result( $result );
			error_log("\n wahyu log " .' result ' .$data_user['notification'], 3, '/home/wahyu/test.cpl') ;
			$id = mysql_insert_id();
			#error_log("\n wahyu log " .' result ' . $id, 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;
		}

		return $id;
	}

}


// ** EDIT USER FROM USER MANAGEMENT IN PORTAL BY IRFAN
function mc_edit_ticket_by_userID($username, $password, $data_user){
	return serialize($username);


}



// ** CREATE TICKET FROM USER ID TO HISTORY TABLE BY IRFAN
function mc_create_ticket_by_userID($username, $password, $data_user){

	if(is_array($data_user)){
		if($data_user['notification'] == 'y'){
			// ** FIND ID FROM USER TABLE
			$queryFind = "select id from mantis_user_table where username = '".$data_user['user_lawas']."' ";
			$result6 =  db_query_bound($queryFind);
			$id = db_result($result6);

			// ** UPDATE USER TABLE
			$queryChange = "UPDATE mantis_user_table
								SET username= '".$data_user['username']."', realname='".$data_user['realname']."',email = '".$data_user['email']."', last_visit = ".time()."
								WHERE id = ".$id."
							";

			$result11 = db_query_bound($queryChange);
			$row11 	= db_result( $result11 );

			// FIND ID FROM EXECUTIVE USERS
			$queryFind2 = "select id from executive_users where user_id = ".$id." ";
			$filterResult = db_query_bound($queryFind2);
			while($p_bug_filter = db_fetch_array ( $filterResult)){
				$project[] = $p_bug_filter;
			}

			foreach($project as $arrKey => $arrValue){
				$queryDelete2 = "DELETE from executive_users where id = ".$arrValue['id']." ";

				$resultDelete2 =  db_query_bound($queryDelete2);
				$row29 = db_result($resultDelete2);
			}

			foreach($data_user['aco_result'] as $dataKey => $dataValue){
				if($dataValue == 'niws'){
					$project_id = 5;
				}
				else if($dataValue == 'changerequests'){
					$project_id = 4;
				}
				else if($dataValue == 'alerts'){
					$project_id = 2;
				}
				else if($dataValue == 'qualities'){
					$project_id = 1;
				}
				else if($dataValue == 'alarms'){
					$project_id = 3;
				}

				$queryEdit = "insert into executive_users(user_id,status,project_id) values(".$id.",1,".$project_id.")";

				$result3 =  db_query_bound($queryEdit);
				$row4 = db_result($result3);

			}

		}

		else if($data_user['notification'] == 'n'){
			// ** FIND ID FROM USER TABLE
			$queryFind = "select id from mantis_user_table where username = '".$data_user['user_lawas']."' ";
			$result6 =  db_query_bound($queryFind);
			$id = db_result($result6);

			$queryChange = "UPDATE mantis_user_table
							SET username= '".$data_user['username']."', realname='".$data_user['realname']."',email = '".$data_user['email']."', last_visit = ".time()."
							WHERE id = ".$id."
						";

			$result11 = db_query_bound($queryChange);
			$row11 	= db_result( $result11 );

			// FIND ID FROM EXECUTIVE USERS
			$queryFind2 = "select id from executive_users where user_id = ".$id." ";
			$filterResult = db_query_bound($queryFind2);
			while($p_bug_filter = db_fetch_array ( $filterResult)){
				$project[] = $p_bug_filter;
			}

			foreach($project as $arrKey => $arrValue){
				$queryDelete2 = "DELETE from executive_users where id = ".$arrValue['id']." ";

				$resultDelete2 =  db_query_bound($queryDelete2);
				$row29 = db_result($resultDelete2);
			}


		}
		else if($data_user['notification'] == 'change'){
			// ** FIND ID FROM USER TABLE
			$queryFind = "select id from mantis_user_table where username = '".$data_user['user_lawas']."' ";
			$result6 =  db_query_bound($queryFind);
			$id = db_result($result6);

			$queryChange = "UPDATE mantis_user_table
							SET username= '".$data_user['username']."', realname='".$data_user['realname']."',email = '".$data_user['email']."',password = '".$data_user['password']."', last_visit = ".time()."
							WHERE id = ".$id."
						";

			$result11 = db_query_bound($queryChange);
			$row11 	= db_result( $result11 );

		}
		else if($data_user['notification'] == 'reset'){
			// ** FIND ID FROM USER TABLE
			$queryFind = "select id from mantis_user_table where username = '".$data_user['username']."' ";
			$result6 =  db_query_bound($queryFind);
			$id = db_result($result6);

			$queryChange = "UPDATE mantis_user_table
							SET username= '".$data_user['username']."',password = '".$data_user['password']."', last_visit = ".time()."
							WHERE id = ".$id."
						";

			$result11 = db_query_bound($queryChange);
			$row11 	= db_result( $result11 );

		}
		else{
			$notive = unserialize($data_user['notification']);
			// ** FIND ID FROM USER TABLE
			$queryFind = "select id from mantis_user_table where username = '".$data_user['user_lawas']."' ";
			$result6 =  db_query_bound($queryFind);
			$id = db_result($result6);

			// ** UPDATE USER TABLE
			$queryChange = "UPDATE mantis_user_table
								SET username= '".$data_user['username']."', realname='".$data_user['realname']."',email = '".$data_user['email']."', last_visit = ".time()."
								WHERE id = ".$id."
							";

			$result11 = db_query_bound($queryChange);
			$row11 	= db_result( $result11 );

			// FIND ID FROM EXECUTIVE USERS
			$queryFind2 = "select id from executive_users where user_id = ".$id." ";
			$filterResult = db_query_bound($queryFind2);
			while($p_bug_filter = db_fetch_array ( $filterResult)){
				$project[] = $p_bug_filter;
			}

			foreach($project as $arrKey => $arrValue){
				$queryDelete2 = "DELETE from executive_users where id = ".$arrValue['id']." ";

				$resultDelete2 =  db_query_bound($queryDelete2);
				$row29 = db_result($resultDelete2);
			}

			foreach($notive as $nt){
				$queryEdit = "insert into executive_users(user_id,status,project_id) values(".$id.",1,".$nt.")";

				$result3 =  db_query_bound($queryEdit);
				$row4 = db_result($result3);

			}
		}

		return $id;

	}

}


// ** GET DATA FROM HISTORY TABLE TABLE BY IRFAN
function mc_get_history($username, $password, $ticketdata){
		$queryFind = "select * from mantis_bug_history_table where  bug_id = ".$ticketdata['id']."  ";
		$result = db_query_bound($queryFind);
		while($p_bug_final = db_fetch_array ( $result)){
			$final_bug[] = $p_bug_final;
		}
		foreach($final_bug as $finalKey => $finalValue){
				if($finalValue['new_value'] != ''){
					$data[$finalKey] = $finalValue;
				}
		}
		return serialize($data);

}

function mc_update_user($username, $password, $data_user){
	if(is_array($data_user)){
		if(!empty($data_user['pwd']))
			$pwd = "password = '".$data_user['pwd']."', ";
		else
			$pwd = '';
	        $queryAdd = "update mantis_user_table SET $pwd realname = '".$data_user['name']."', email = '".$data_user['email']."' where username = '".$data_user['username']."'";
	        $result = db_query_bound($queryAdd);
	        $row 	= db_result( $result );


		return $row;
	}

}

// * Get quality alert fitrah ajah
function mc_quality_alert( $p_username, $p_password, $field,$value=NULL,$posisi=NULL,$batas=NULL,$where=null ) {
	// ** QUERY TO INSERT COIN TABLE\
	$posisi = (empty($posisi)) ? 0 : $posisi;
	$batas = (empty($batas)) ? 30 : $batas;
	
	switch ($field) {
		case 'domain': $query = "select domain from quality_alert group by domain";
		break;
		case 'unit' : $query = "select unit from quality_alert where domain = '$value'
		group by unit";
		break;
		case 'subsystem' : $query = "select subsystem from quality_alert where unit = '$value'
		group by subsystem";
		break;
		case 'all' : $query = "select * from quality_alert limit $posisi,$batas";
		break;
		case 'condition' : $query = "select * from quality_alert $where limit $posisi,$batas";
		break;
		case 'count' : $query = "select * from quality_alert";
		break;
	}
	$result = db_query_bound($query);
		while($quality_alert = db_fetch_array ( $result)){
			$results[] = $quality_alert;
	}
	return serialize($results);
}

function mc_quality_alert_new( $p_username, $p_password,$data) {
	
	$posisi = (empty($data['posisi'])) ? 0 : $data['posisi'];
	$batas = (empty($data['batas'])) ? 30 : $data['batas'];
	$where = $data['where'];
	$field = $data['field'];
	
	switch ($field) {
		case 'all' :
			$query = "select * from quality_alert limit $posisi,$batas";
		break;
		case 'condition' :
			$query = "select * from quality_alert $where limit $posisi,$batas";
		break;
		case 'count' :
			$query = "select * from quality_alert $where";
		break;
		case 'delete' :
			$query = "delete from quality_alert $where";
		break;
		case 'update' :
			$query = "update quality_alert $where";
		break;
	}
	$result = db_query_bound($query);
		while($quality_alert = db_fetch_array ( $result)){
			$results[] = $quality_alert;
		}
	//error_log("$query ".print_r($query,1)." ",3,"/opt/serverman/api/soap/error.log");
	//error_log("$query ".print_r($data,1)." ",3,"/opt/serverman/api/soap/error.log");
	return serialize($results);
}


// * Get quality alert for hardware fitrah ajah
function mc_quality_alert_hardware( $p_username, $p_password, $field,$value=NULL,$posisi=NULL,$batas=NULL,$where=null ) {
	// ** QUERY TO INSERT COIN TABLE\
	$posisi = (empty($posisi)) ? 0 : $posisi;
	$batas = (empty($batas)) ? 30 : $batas;
	
	switch ($field) {
		case 'domain': $query = "select domain from quality_alert_hardware group by domain";
		break;
		case 'unit' : $query = "select unit from quality_alert_hardware where domain = '$value'
		group by unit";
		break;
		case 'subsystem' : $query = "select subsystem from quality_alert_hardware where unit = '$value'
		group by subsystem";
		break;
		case 'all' : $query = "select * from quality_alert_hardware limit $posisi,$batas";
		break;
		case 'condition' : $query = "select * from quality_alert_hardware $where limit $posisi,$batas";
		break;
		case 'count' : $query = "select * from quality_alert_hardware";
		break;
	}
	$result = db_query_bound($query);
		while($quality_alert = db_fetch_array ( $result)){
			$results[] = $quality_alert;
	}
	error_log("$query ".print_r($query,1)." ",3,"/opt/serverman/api/soap/error.log");
	error_log("$query ".print_r($data,1)." ",3,"/opt/serverman/api/soap/error.log");
	return serialize($results);
}

function mc_quality_alert_hardware_new( $p_username, $p_password,$data) {
	
	$posisi = (empty($data['posisi'])) ? 0 : $data['posisi'];
	$batas = (empty($data['batas'])) ? 30 : $data['batas'];
	$where = $data['where'];
	$field = $data['field'];
	
	switch ($field) {
		case 'all' :
			$query = "select * from quality_alert_hardware limit $posisi,$batas";
		break;
		case 'condition' :
			$query = "select * from quality_alert_hardware $where limit $posisi,$batas";
		break;
		case 'count' :
			$query = "select * from quality_alert_hardware $where";
		break;
		case 'delete' :
			$query = "delete from quality_alert_hardware $where";
		break;
		case 'update' :
			$query = "update quality_alert_hardware $where";
		break;
	}
	$result = db_query_bound($query);
		while($quality_alert = db_fetch_array ( $result)){
			$results[] = $quality_alert;
		}
	error_log("$query ".print_r($query,1)." ",3,"/opt/serverman/api/soap/error.log");
	error_log("$query ".print_r($data,1)." ",3,"/opt/serverman/api/soap/error.log");
	return serialize($results);
}


function mc_cr_hardware( $p_username, $p_password, $field,$value=NULL,$posisi=NULL,$batas=NULL,$where=null ) {
	// ** QUERY TO INSERT COIN TABLE\
	$posisi = (empty($posisi)) ? 0 : $posisi;
	$batas = (empty($batas)) ? 30 : $batas;
	
	switch ($field) {
		case 'domain': $query = "select r_type from cr_hardware group by r_type";
		break;
		case 'unit' : $query = "select n_type,sla from cr_hardware where r_type = '$value'
		group by n_type";
		break;
		case 'subsystem' : $query = "select sla from cr_hardware where n_type = '$value'
		group by sla";
		break;
		case 'all' : $query = "select * from cr_hardware limit $posisi,$batas";
		break;
		case 'condition' : $query = "select * from cr_hardware $where limit $posisi,$batas";
		break;
		case 'count' : $query = "select * from cr_hardware";
		break;
	}
	$result = db_query_bound($query);
		while($quality_alert = db_fetch_array ( $result)){
			$results[] = $quality_alert;
	}
	error_log("$query ".print_r($query,1)." ",3,"/opt/serverman/api/soap/error.log");
	error_log("$query ".print_r($data,1)." ",3,"/opt/serverman/api/soap/error.log");
	return serialize($results);
}

function mc_cr_hardware_new( $p_username, $p_password,$data) {
	
	$posisi = (empty($data['posisi'])) ? 0 : $data['posisi'];
	$batas = (empty($data['batas'])) ? 30 : $data['batas'];
	$where = $data['where'];
	$field = $data['field'];
	
	switch ($field) {
		case 'all' :
			$query = "select * from cr_hardware limit $posisi,$batas";
		break;
		case 'condition' :
			$query = "select * from cr_hardware $where limit $posisi,$batas";
		break;
		case 'count' :
			$query = "select * from cr_hardware $where";
		break;
		case 'delete' :
			$query = "delete from cr_hardware $where";
		break;
		case 'update' :
			$query = "update cr_hardware $where";
		break;
	}
	$result = db_query_bound($query);
		while($quality_alert = db_fetch_array ( $result)){
			$results[] = $quality_alert;
		}
	//error_log("$query ".print_r($query,1)." ",3,"/opt/serverman/api/soap/error.log");
	//error_log("$query ".print_r($data,1)." ",3,"/opt/serverman/api/soap/error.log");
	return serialize($results);
}

// Get Table coin for NE NAME Fitrah
// @deprecated in favor of COIN_TABLE constant
function mc_get_cointable() {
	// ** QUERY TO GET Coin Table

	$query = "select coin_table as name from last_coin order by id desc limit 0,1";
	$result = db_query_bound($query);
	$row 	= db_result( $result );
	return $row;

}

//Get list coin fitrah
function mc_get_coin($username, $password, $data){
	// FIND USER ID FROM MANTIS USER TABLE
	$data = unserialize($data);

	if(is_array($data)){
		foreach($data as $key => $dat){
			if(!empty($dat))
				$where[] = " $key LIKE '%$dat%'";
		}
		if(is_array($where))
			$where = "Where ".implode(" OR ", $where);
	}

	$query = "SELECT * FROM ".COIN_TABLE." $where";
	
	$result = db_query_bound($query);
		while($coin = db_fetch_array ( $result)){
			$results[] = $coin;
	}
	return serialize($results);
}

// Get NE NAME Fitrah
function mc_get_nename( $p_username, $p_password,$field ) {
	// ** QUERY TO GET NE NAME

	$table = COIN_TABLE;
	switch ($field) {
		case 'BSC':$query = "select distinct(bsc_name) as name from $table";
		break;
		case 'BTS' : $query = "select distinct(bts_name) as name from $table";
		break;
		case 'cell' : $query = "select distinct(cellname) as name from $table";
		break;
		case 'ci': $query = "select distinct(ci) as name from $table";
		break;
		case 'ci&bts': $query = "select distinct(ci) as id,CONCAT(ci, ' / ', bts_name) as name from $table";
		break;
		case 'ci&cell': $query = "select distinct(ci) as id,CONCAT(ci, ' / ', cellname) as name from $table";
		break;
	}
	$result = db_query_bound($query);
		while($nename = db_fetch_array ( $result)){
			$results[] = $nename;
	}
	return serialize($results);
}

function get_last_id($username, $password){
	 $query = "SELECT bug.id
FROM  `mantis_bug_table` bug
LEFT JOIN mantis_custom_field_string_table custom ON custom.bug_id = bug.id
AND custom.field_id =28
LEFT JOIN mantis_custom_field_string_table cst ON cst.bug_id = bug.id
AND cst.field_id =2
AND cst.value >1371200427
WHERE bug.project_id =1 AND bug.status>30
AND custom.value IS NULL 
ORDER BY bug.id DESC 
LIMIT 1";
                $result = db_query_bound($query);
                $id  = db_result( $result );
		return $id;

}

//insert quality alert by fitrah
function mc_insert_quality_alert($username, $password, $data){
	
	$queryAdd = "INSERT INTO quality_alert
			values ('".$data['domain']."','".$data['unit']."','".$data['subsystem']."') ";
	$result2 = db_query_bound($queryAdd);
	$row 	= db_result( $result2 );

	return $row;
}

function mc_insert_quality_alert_hardware($username, $password, $data){
	
	$queryAdd = "INSERT INTO quality_alert_hardware
			values ('".$data['domain']."','".$data['unit']."','".$data['subsystem']."') ";
	$result2 = db_query_bound($queryAdd);
	$row 	= db_result( $result2 );

	return $row;
}

function mc_insert_cr_hardware($username, $password, $data){
	
	$queryAdd = "INSERT INTO cr_hardware
			values ('".$data['domain']."','".$data['unit']."','".$data['subsystem']."') ";
	$result2 = db_query_bound($queryAdd);
	$row 	= db_result( $result2 );

	return $row;
}

function getDaysInWeek ($weekNumber, $year) {

  // Count from '0104' because January 4th is always in week 1
  // (according to ISO 8601).
  $time = strtotime($year . '0104 +' . ($weekNumber - 1)
                    . ' weeks');
  // Get the time of the first day of the week
  $mondayTime = strtotime('-' . (date('w', $time) - 1) . ' days',
                          $time);
  // Get the times of days 0 -> 6
  $dayTimes = array ();
  for ($i = 0; $i < 7; ++$i) {
    $dayTimes[] = strtotime('+' . $i . ' days', $mondayTime);
  }
  // Return timestamps for mon-sun.
  return $dayTimes;
}

function mc_issue_chart($p_username, $p_password,$filterdata){

	$arr = array();
	#error_log("$p_username  $p_password ".print_r($filterdata,1)." ",3,"/opt/serverman/api/soap/error.log");
	if(!empty($p_username) && !empty($p_password)){

		switch($filterdata['type']){
			case "dialy":
				$start = $filterdata['date']." 00:00:00";
				$end = $filterdata['date']." 23:59:59";
				$groups = "HOUR";
				$fields = "$groups( FROM_UNIXTIME( custom.value ) ) AS day";
				$group_by = "$groups( FROM_UNIXTIME( custom.value ) )";
				break;
			default:
			case "monthly":
				$start = $filterdata['year']."-".$filterdata['month']."-01 00:00:00";
				$end = $filterdata['year']."-".$filterdata['month']."-".cal_days_in_month(CAL_GREGORIAN, $filterdata['month'], $filterdata['year'])." 23:59:59";
				$fields = "coin.branch as branch, coin.bts_name";
				$leftjoin = "LEFT JOIN mantis_custom_field_string_table mcfs ON mcfs.bug_id = bug.id and mcfs.field_id = 29
						LEFT JOIN coin20120801 coin on coin.id = mcfs.value";
				$group_by = "coin.branch";
				$groups = "DAY";
				break;
			case "weekly":
				$dayTimes = getDaysInWeek($filterdata['weekly'], $filterdata['year']);
                                $start = date("Y-m-d", $dayTimes[0])." 00:00:00";
                                $end = date("Y-m-d", $dayTimes[6])." 23:59:59";
                                $groups = "DAY";
				$month_ = date("m", $dayTimes[0]);
				$arr['from_to'] = "From ".date("d-M-Y", $dayTimes[0])." to ".date("d-M-Y", $dayTimes[6]);
				$fields = "$groups( FROM_UNIXTIME( custom.value ) ) AS day";
				$group_by = "$groups( FROM_UNIXTIME( custom.value ) )";
                                break;
		}


		$query = "SELECT COUNT( * ) AS jml, project.name, 10 AS
				status , $fields, MONTH( FROM_UNIXTIME( custom.value ) ) as bulan
				FROM  mantis_bug_table bug
				LEFT JOIN `mantis_custom_field_string_table` custom ON custom.bug_id = bug.id AND custom.`field_id` =  '2'
				LEFT JOIN mantis_project_table project ON project.id = bug.project_id
				$leftjoin
				WHERE custom.`value` >= (
				SELECT UNIX_TIMESTAMP(  '".$start."' ) )
				AND  custom.`value` <= (
				SELECT UNIX_TIMESTAMP(  '".$end."' ) )
				AND custom.`value` != ''
				AND bug.status < '80'
				AND project.name IS NOT NULL
				GROUP BY $group_by , project.name

				UNION ALL

				SELECT COUNT( * ) AS jml, project.name, 90 AS
				status , $fields, MONTH( FROM_UNIXTIME( custom.value ) ) as bulan
				FROM  mantis_bug_table bug
				LEFT JOIN `mantis_custom_field_string_table` custom ON custom.bug_id = bug.id AND custom.`field_id` =  '3'
				LEFT JOIN mantis_project_table project ON project.id = bug.project_id
				$leftjoin
				WHERE  custom.`value` >= (
				SELECT UNIX_TIMESTAMP(  '".$start."' ) )
				AND  custom.`value` <= (
				SELECT UNIX_TIMESTAMP(  '".$end."' ) )
				AND custom.`value` != ''
				AND project.name IS NOT NULL
				AND bug.status = '90'
				GROUP BY $group_by , project.name";
		error_log("\n===========\n",3,"/opt/serverman/api/soap/error.log");
		//error_log("$query",3,"/opt/serverman/api/soap/error.log");

		$result = db_query_bound($query);
		$branch = array();
		while($bug = db_fetch_array ( $result)){
		//error_log(print_r($bug, 1),3,"/opt/serverman/api/soap/error.log");

			if($filterdata['type']=='monthly'){
				if(!empty($bug['branch'])){
					//buat slot

					$arr[$bug['name']][$bug['status']][$bug['branch']] += $bug['jml'];

					if(!in_array($bug['branch'],$arr[$bug['name']][$bug['status']],1)){
						$arr[$bug['name']][$bug['status']][] = $bug['branch'];
					}

					$opos = ($bug['status']==10)?90:10;
					if(!isset($arr[$bug['name']][$opos][$bug['branch']])) {
						$arr[$bug['name']][$opos][$bug['branch']] = 0;
					}else{

					}

					if(!in_array($bug['branch'],$arr[$bug['name']][$opos],1)){
						$arr[$bug['name']][$opos][] = $bug['branch'];
					}

				}
			}else{
				$arr[$bug['name']][$bug['status']][$bug['day']] = $bug['jml'];
				if($bug['bulan'] > 0)
					$arr[$bug['name']]['bulan'][$bug['day']] = $bug['bulan'];
		}
	}
	}


	return json_encode($arr);
}

function audit_trail_list($username, $password, $data){

	$arr = '';

	if(!empty($username) && !empty($password)){

		if($data['project']!='all')
			$where = " AND project.id = ".$data['project'];
		if(!empty($data['data']['date1']) && empty($data['data']['date1'])){
			$date = substr($data['data']['date1'],-2);
	                $month = substr($data['data']['date1'],5,2);
        	        $year = substr($data['data']['date1'],0,4);
                	$merge_time_first = $year."-".$month."-".$date." 00:00:00";
			$merge_time_end = $year."-".$month."-".$date." 23:59:59";
			$where .= " AND cfs.value BETWEEN UNIX_TIMESTAMP('".$merge_time_first."') AND UNIX_TIMESTAMP('".$merge_time_end."')";
		}else if(!empty($data['data']['date1']) && !empty($data['data']['date1'])){
			$date = substr($data['data']['date1'],-2);
                        $month = substr($data['data']['date1'],5,2);
                        $year = substr($data['data']['date1'],0,4);
                        $merge_time_first = $year."-".$month."-".$date." 00:00:00";

			$date = substr($data['data']['date2'],-2);
                        $month = substr($data['data']['date2'],5,2);
                        $year = substr($data['data']['date2'],0,4);
                        $merge_time_end = $year."-".$month."-".$date." 23:59:59";
                        $where .= " AND cfs.value BETWEEN UNIX_TIMESTAMP('".$merge_time_first."') AND UNIX_TIMESTAMP('".$merge_time_end."')";
		}
		$query_jumlah = "SELECT count(*) as jumlah
				FROM  `mantis_bug_history_table` history
				LEFT JOIN mantis_bug_table bug ON bug.id = history.bug_id
				LEFT JOIN mantis_project_table project ON project.id = bug.project_id
				LEFT JOIN mantis_user_table user ON user.id = history.user_id
				LEFT JOIN  `mantis_custom_field_string_table` cfs ON cfs.bug_id = bug.id AND cfs.field_id = 2
				WHERE history.field_name != '' and project.id != ''
				$where
				GROUP BY history.bug_id ";
		$result_jumlah = db_query_bound($query_jumlah);
		while($jml = db_fetch_array ( $result_jumlah)){
			$jumlah_data[] = $jml;
		}

		$arr['jumlah'] = count($jumlah_data);

		$batas = 30;

		if($data['halaman'])
			$halaman = ($data['halaman'] - 1) * $batas;
		else
			$halaman = 0;

		/*$halaman = $data['halaman']==''?'0':($data['halaman']-1);

		$halaman = ($halaman*10) + 1;
		*/
			$query = "SELECT history. * , bug.id, project.name, project.id as project_id, MAX( date_modified ) AS timedate, user.realname, cfs.value as start_date
					FROM  `mantis_bug_history_table` history
					LEFT JOIN mantis_bug_table bug ON bug.id = history.bug_id
					LEFT JOIN mantis_project_table project ON project.id = bug.project_id
					LEFT JOIN mantis_user_table user ON user.id = history.user_id
					LEFT JOIN  `mantis_custom_field_string_table` cfs ON cfs.bug_id = bug.id AND cfs.field_id = 2
					WHERE history.field_name != '' and project.id != '' $where
					GROUP BY history.bug_id
					ORDER BY timedate DESC
					LIMIT $halaman,$batas";
		$result = db_query_bound($query);
		while($bug = db_fetch_array ( $result)){
			$arr['data'][] = $bug;
		}

	}

	return json_encode($arr);
}

function loging_list($username, $password, $data){

	$arr = '';

	if(!empty($username) && !empty($password)){

		if(!empty($data['data']['log_id'])){
			$where .= " AND history.id = '".$data['data']['log_id']."'";
		}
		if(!empty($data['data']['severity'])){
			$where .= " AND bug.severity = '".$data['data']['severity']."'";
		}
		if(!empty($data['data']['area'])){
			$where .= " AND coin.area like '%".$data['data']['area']."%'";
		}
		if(!empty($data['data']['address'])){
			$where .= " AND coin.address like '%".$data['data']['address']."%'";
		}
		if(!empty($data['data']['no_ticket'])){
			$where .= " AND bug.id = '".$data['data']['no_ticket']."'";
		}
		if(!empty($data['data']['ne_name'])){
			$where .= " AND coin.ci like '%".$data['data']['ne_name']."%'";
		}
		if(!empty($data['data']['description'])){
			$where .= " AND bug.summary like '%".$data['data']['description']."%'";
		}
		if(!empty($data['data']['indosat_id'])){
			$where .= " AND cfs2.value like '%".$data['data']['indosat_id']."%'";
		}

		if($data['project']!='all')
			$where .= " AND project.id = ".$data['project'];
		else
			$where .= " AND project.id IN (1,3,4)";
			
		if(!empty($data['data']['date1']) && empty($data['data']['date1'])){
			$date = substr($data['data']['date1'],-2);
	                $month = substr($data['data']['date1'],5,2);
        	        $year = substr($data['data']['date1'],0,4);
                	$merge_time_first = $year."-".$month."-".$date." 00:00:00";
			$merge_time_end = $year."-".$month."-".$date." 23:59:59";
			$where .= " AND cfs.value BETWEEN UNIX_TIMESTAMP('".$merge_time_first."') AND UNIX_TIMESTAMP('".$merge_time_end."')";
		}else if(!empty($data['data']['date1']) && !empty($data['data']['date1'])){
			$date = substr($data['data']['date1'],-2);
                        $month = substr($data['data']['date1'],5,2);
                        $year = substr($data['data']['date1'],0,4);
                        $merge_time_first = $year."-".$month."-".$date." 00:00:00";

			$date = substr($data['data']['date2'],-2);
                        $month = substr($data['data']['date2'],5,2);
                        $year = substr($data['data']['date2'],0,4);
                        $merge_time_end = $year."-".$month."-".$date." 23:59:59";
                        $where .= " AND cfs.value BETWEEN UNIX_TIMESTAMP('".$merge_time_first."') AND UNIX_TIMESTAMP('".$merge_time_end."')";
		}
		$query_jumlah = "SELECT count(*) as jumlah
				FROM  `mantis_log_result` history
				LEFT JOIN mantis_bug_table bug ON bug.id = history.bug_id
				LEFT JOIN mantis_project_table project ON project.id = bug.project_id
				LEFT JOIN mantis_user_table user ON user.username = history.username
				LEFT JOIN  `mantis_custom_field_string_table` cfs ON cfs.bug_id = bug.id AND cfs.field_id = 2
					LEFT JOIN  `mantis_custom_field_string_table` cfs1 ON cfs1.bug_id = bug.id AND cfs1.field_id = 27
					LEFT JOIN  `mantis_custom_field_string_table` cfs2 ON cfs2.bug_id = bug.id AND cfs2.field_id = 28
					LEFT JOIN  `mantis_custom_field_string_table` cfs3 ON cfs3.bug_id = bug.id AND cfs3.field_id = 29
					LEFT JOIN  `mantis_custom_field_string_table` cfs4 ON cfs4.bug_id = bug.id AND cfs4.field_id = 30
					LEFT JOIN  `mantis_custom_field_string_table` cfs5 ON cfs5.bug_id = bug.id AND cfs5.field_id = 31
					LEFT JOIN  `mantis_custom_field_string_table` cfs6 ON cfs6.bug_id = bug.id AND cfs6.field_id = 32
					LEFT JOIN  `mantis_custom_field_string_table` cfs7 ON cfs7.bug_id = bug.id AND cfs7.field_id = 33
					LEFT JOIN  `mantis_custom_field_string_table` cfs8 ON cfs8.bug_id = bug.id AND cfs8.field_id = 34
					LEFT JOIN  `".COIN_TABLE."` coin ON coin.id=cfs3.value
				WHERE 1=1 $where ";
		$result_jumlah = db_query_bound($query_jumlah);
		/*while($jml = db_fetch_array ( $result_jumlah)){
			$jumlah_data[] = $jml;
		}*/

		$arr['jumlah'] = db_result( $result_jumlah );

		$batas = 30;

		if($data['halaman'])
			$halaman = ($data['halaman'] - 1) * $batas;
		else
			$halaman = 0;

		/*$halaman = $data['halaman']==''?'0':($data['halaman']-1);

		$halaman = ($halaman*10) + 1;
		*/
			$query = "SELECT history.* , coin.*,bug.id,bug.severity,bug.summary, project.name, project.id as project_id, user.realname, cfs.value as start_date, cfs1.value as optional1, cfs2.value as optional2, cfs3.value as optional3, cfs4.value as optional4, cfs5.value as subsystem, cfs6.value as domain_quality, cfs7.value as unit_quality, cfs8.value as subsystem_quality, history.id as history_id
					FROM  `mantis_log_result` history
					LEFT JOIN mantis_bug_table bug ON bug.id = history.bug_id
					LEFT JOIN mantis_project_table project ON project.id = bug.project_id
					LEFT JOIN mantis_user_table user ON user.username = history.username
					LEFT JOIN  `mantis_custom_field_string_table` cfs ON cfs.bug_id = bug.id AND cfs.field_id = 2
					LEFT JOIN  `mantis_custom_field_string_table` cfs1 ON cfs1.bug_id = bug.id AND cfs1.field_id = 27
					LEFT JOIN  `mantis_custom_field_string_table` cfs2 ON cfs2.bug_id = bug.id AND cfs2.field_id = 28
					LEFT JOIN  `mantis_custom_field_string_table` cfs3 ON cfs3.bug_id = bug.id AND cfs3.field_id = 29
					LEFT JOIN  `mantis_custom_field_string_table` cfs4 ON cfs4.bug_id = bug.id AND cfs4.field_id = 30
					LEFT JOIN  `mantis_custom_field_string_table` cfs5 ON cfs5.bug_id = bug.id AND cfs5.field_id = 31
					LEFT JOIN  `mantis_custom_field_string_table` cfs6 ON cfs6.bug_id = bug.id AND cfs6.field_id = 32
					LEFT JOIN  `mantis_custom_field_string_table` cfs7 ON cfs7.bug_id = bug.id AND cfs7.field_id = 33
					LEFT JOIN  `mantis_custom_field_string_table` cfs8 ON cfs8.bug_id = bug.id AND cfs8.field_id = 34
					LEFT JOIN  `".COIN_TABLE."` coin ON coin.id=cfs3.value
					WHERE 1=1 $where
					ORDER BY history.actual_date DESC
					LIMIT $halaman,$batas";
		$result = db_query_bound($query);
		while($bug = db_fetch_array ( $result)){
			$arr['data'][] = $bug;
		}

		$arr['sql'] = $query_jumlah." # ".$query;

	}

	return json_encode($arr);
}

function get_last_coin_table(){
	$coin = '';

	$query = "select coin_table as name from last_coin order by id desc limit 0,1";
        $result = db_query_bound($query);
        $row    = db_result( $result );
	//error_log("\n wahyu log " .print_r($row, 1), 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;
        return $row;
}


// ** GET DATA IN AUDIT TRAIL BY IRFAN
function audit_trail_get($username, $password, $data){

	$arr = '';

	$query = "select history.*,project.name ,bug.status ,bug.severity ,  users.username from mantis_bug_history_table history                                         LEFT JOIN mantis_bug_table bug ON bug.id = history.bug_id  LEFT JOIN mantis_project_table project ON project.id = bug.project_id               LEFT JOIN mantis_user_table users ON users.id = history.user_id WHERE project.id = ".$data['project']." AND history.bug_id = '".$data['historyID']."' ";
		/*"select history.*,project.name ,bug.status ,bug.severity , string.value ,field.name , users.username from mantis_bug_history_table history
					LEFT JOIN mantis_bug_table bug ON bug.id = history.bug_id
						LEFT JOIN mantis_project_table project ON project.id = bug.project_id
								LEFT JOIN mantis_custom_field_string_table string ON string.bug_id = bug.id
									LEFT JOIN mantis_custom_field_table field on field.id = string.field_id
											LEFT JOIN mantis_user_table users ON users.id = history.user_id
												WHERE project.id = ".$data['project']." AND history.bug_id = '".$data['historyID']."'
								";*/

	$result = db_query_bound($query);
	while($bug = db_fetch_array ( $result)){
		$arr['data'][] = $bug;
	}

	$project = array();
	foreach($arr['data'] as $arrKey => $arrValue){
		if($arrValue['bug_id'] == $data['historyID']){
				$project[] = $arrValue;

		}
	}

	return json_encode($project);
}
// ** GET DATA IN LOG RESULT BY Wahyu
function log_result($username, $password, $data){

	$arr = '';

		if(!empty($data['id']))
			$WHERE[] = " log.id = ".$data['id'];

		if(!empty($data['theDate']))
			$WHERE[] = " log.actual_date = '".$data['theDate']."'";

		if(!empty($data['indosat_id']))
			$WHERE[] = " custom.value like '%".$data['indosat_id']."%'";

		if(!empty($data['number']))
			$WHERE[] = " bug.id like '%".$data['number']."%'";

		if(!empty($data['from_to']))
			$WHERE[] = " log.from = ".$data['from_to'];

		if(!empty($data['type']))
			$WHERE[] = " project.id = ".$data['type'];

		if(is_array($WHERE))
			$WHERE = implode(" AND ", $WHERE);

		if(!empty($WHERE))
			$WHERE = " where ".$WHERE; 

		$query_jumlah = "SELECT count(*) as jumlah FROM `mantis_log_result` log
			LEFT JOIN mantis_bug_table bug on bug.id=log.bug_id
			LEFT JOIN mantis_project_table project ON project.id = bug.project_id
			LEFT JOIN mantis_custom_field_string_table custom on custom.bug_id = bug.id AND custom.field_id = 28
			$WHERE
			ORDER BY date desc ";
		$result = db_query_bound($query_jumlah);
		$arr['jumlah'] 	= db_result( $result );
		
		$batas = 30;

		if($data['halaman'])
			$halaman = ($data['halaman'] - 1) * $batas;
		else
			$halaman = 0;

	$query = "SELECT log.* ,project.name ,bug.status ,bug.severity, bug.id as bug_id, custom.value as val FROM `mantis_log_result` log
			LEFT JOIN mantis_bug_table bug on bug.id=log.bug_id
			LEFT JOIN mantis_project_table project ON project.id = bug.project_id
			LEFT JOIN mantis_custom_field_string_table custom on custom.bug_id = bug.id AND custom.field_id = 28
			$WHERE
			ORDER BY date desc
			LIMIT $halaman, $batas
			";
		

	$result = db_query_bound($query);
	while($bug = db_fetch_array ( $result)){
		$arr['data'][] = $bug;
	}
	$arr['sql'] = $query;
	return json_encode($arr);
}

// ** MONITORING LIST USERS AND TICKET BY IRFAN
function mc_monitoring_list($username, $password, $data){
	// FIND USER ID FROM MANTIS USER TABLE
	$queryFind = "select * from mantis_user_table where username = '".$username."'";
	$result = db_query_bound($queryFind);
	$userId 	= db_result( $result );
/*
	$queryAdd = "INSERT INTO mantis_bug_monitor_table
			values ('".$userId."','".$data['id']."') ";
	$result2 = db_query_bound($queryAdd);
	$row 	= db_result( $result2 );
*/
	return $row;
}


// ** UNMONITORING TICKET BY IRFAN
function mc_unmonitoring_ticket($username, $password,  $data){
	/*
	// FIND USER ID FROM MANTIS USER TABLE
	$queryFind = "DELETE from mantis_bug_monitor_table where bug_id = ".$data['id']." ";
	$result = db_query_bound($queryFind);
	$row 	= db_result( $result );
	*/
	// ** FIND ID FROM USER TABLE
	$queryFind = "select id from mantis_user_table where username = '".$data['username']."' ";
	$result6 =  db_query_bound($queryFind);
	$id = db_result($result6);

	// FIND ID FROM EXECUTIVE USERS
	$queryFind2 = "select project_id from executive_users where user_id = ".$id." ";
	$filterResult = db_query_bound($queryFind2);

	while($b = db_fetch_array ( $filterResult)){
             $arr[] = $b['project_id'];
        }


	//$idUsers = db_result($filterResult);


	return serialize($arr);
}

// ** CHECK TICKET BY IRFAN
function mc_monitoring_check($username, $password, $ticketID){
	// FIND USER ID FROM MANTIS USER TABLE
	$queryFind = "select count(bug_id) from mantis_bug_monitor_table  where bug_id = ".$ticketID." ";
	$result = db_query_bound($queryFind);
	$row 	= db_result( $result );

	return $row;
}

// ** CHECK IF MANY CHANGES IN TICKET
function mc_monitoring_ticket($username, $password, $data){
	// FIND USER ID FROM MANTIS USER TABLE
	$queryFind = "select * from mantis_user_table where username = '".$username."'";
	$result = db_query_bound($queryFind);
	$userId 	= db_result( $result );

	//CHECK USER ID AND BUG_ID
	$queryfind2= "SELECT b.* FROM mantis_bug_monitor_table a
						LEFT JOIN mantis_bug_history_table b ON b.bug_id = a.bug_id
							where b.user_id = '".$userId."'
				";
	$result5 = db_query_bound($queryfind2);
	while($bug = db_fetch_array ( $result5)){
		$arr[] = $bug;
	}

	foreach($arr as $arrKey => $arrValue){
		if($arrValue['new_value'] != ""){
			$message[] = "Change <b>".$arrValue['field_name']."</b> From <b>".$arrValue['old_value']."</b>
								To <b>".$arrValue['new_value']."</b>";
		}
	}

	if(!empty($message)){
		foreach($message as $messageKey => $messageValue){
			$message = $messageValue;
		}
	}
	$to      = 'irfan@localhost';
	$subject = 'Monitoring Ticket';
	$headers = 'From: webmaster@example.com';
	mail($to, $subject, $message, $headers);

	return serialize($arr);
}

function mc_get_relationship_id($username, $password, $bug_id){
	$destination= "SELECT distinct(destination_bug_id) FROM `mantis_bug_relationship_table` where source_bug_id = '$bug_id' ";
        $res_destination = db_query_bound($destination);
        $bug = db_result($res_destination);

	if(!$bug){
	        $bug = $bug_id;
	}

	$queryfind2= "SELECT source_bug_id
                          FROM  `mantis_bug_relationship_table`
                              WHERE  `destination_bug_id` = '$bug'";
        $result2 = db_query_bound($queryfind2);
        while($bug_data = db_fetch_array ( $result2)){
             $arr[] = $bug_data['source_bug_id'];
        }

	if(is_array($arr)){
	     $arr[] = $bug;
	}

	return serialize($arr);
}

function get_related_temip_remedy($ao_id){
	$sql = "SELECT mcfst.`bug_id` 
		FROM  `mantis_custom_field_string_table` mcfst
		LEFT JOIN mantis_custom_field_table mcft ON mcfst.field_id = mcft.id
		WHERE mcfst.`value` =  '$ao_id'
		AND mcft.id =  '28'";

	$res = db_query_bound($sql);
        $bug_id = db_result($res);

	return $bug_id;

}

function log_result_add($username, $password, $arguments){
	$queryAdd = "INSERT INTO mantis_log_result
(bug_id,username,`from`,`action`,activity,message,actual_date,`date`) values
('".$arguments['bug_id']."','".$arguments['username']."','".$arguments['from']."','".$arguments['action']."','".$arguments['activity']."','".$arguments['message']."','".$arguments['actual_date']."','".date('Y-m-d H:i:s')."')";
		//error_log("\n wahyu log " . $queryAdd, 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;

	        $result = db_query_bound($queryAdd);
        	$row    = db_result( $result );

		return $queryAdd;
}

//-------------------------REMEDY WSDL----------------------//

	/**
	 * Alarm Trouble Ticket Add..
	 *
	 * @param string $username A username
	 * @param string $password A password
	 * @param string $arguments A arguments
	 * @return string Response
	 */
 	function at_add($username, $password, $arguments){
		$api_status = "SUCCESS";
		$api_error = "";
		$api_description = "";
		$pwd = array(
			"remedyransharing" => "HI$[`K.f'Fr]DrCY"
		);


		if (empty($username) OR empty($password)) {
			$api_status = "FAILED";
			$api_error = "Invalid credential";
		} elseif (!array_key_exists($username, $pwd)) {
			$api_status = "FAILED";
			$api_error = "Invalid user";
		} else {
			if ($pwd[$username] != $password) {
				$api_status = "FAILED";
				$api_error = "Invalid password";
			}
		}

error_log(date('YmdHis - ') . $arguments, 3, '/tmp/at_add.log');

		if(empty($api_error)){

			$arguments = json_decode(strtojson($arguments));

			if(strtolower($arguments->status)=='new'){
				$status = 10;
			}else if(strtolower($arguments->status)=='assigned'){
				$status = 50;
			}else if(strtolower($arguments->status)=='in progress'){
				$status = 60;
			/*
			}else if(strtolower($arguments->status)=='pending'){
				$status = 70;
			*/
			}else if(strtolower($arguments->status)=='resolved'){
				$status = 80;
			}else if(strtolower($arguments->status)=='closed'){
				$status = 90;
			}else if(strtolower($arguments->status)=='canceled'){
				$status = 75;
			}

			switch (strtolower($arguments->severity))
			{
			case 'new':
			case 'critical':
			case 'major':
			case 'minor':
				$severity = strtolower($arguments->severity);
			}

			switch ($arguments->severity_ran)
			{
			case 'P1':
			case 'P2':
			case 'P3':
				$severity = $arguments->severity = strtolower($arguments->severity_ran);
			}

			$date_submited =  strtotime($arguments->start_date);

			$issueData = array(
				'category'        => 'Alarm Troubleticket',
				'reproducibility' => 'always',
				'severity' 	  => array( 'name' => $severity ),
				'status' 	  => array( 'id' => $status ),
				'description' 	  => $arguments->problem,
				'summary' 	  => $arguments->alarm_name,
				'custom_fields'   => array(
					array('field' => array('id' => '2'),  'value' => $date_submited),        //start date
					array('field' => array('id' => '27'), 'value' => $arguments->ne_name),   //ne_name
					array('field' => array('id' => '28'), 'value' => $arguments->indosat_ticket_id), //remedy_id
                    			array('field' => array('id' => '29'), 'value' => mc_issue_get_coin_id('', '', array(
                        'name'=>'bsc_name', 'value'=>$arguments->ne_name
                    ))), // coin
					array('field' => array('id' => '30'), 'value' => $arguments->case_id), //case_id
				),
				'project' => array( 'id' =>  3 )
			);

			// check related with alarm temip
			$alarm_id = get_related_temip_remedy($arguments->AO_ID);
			//if($alarm_id>0){
				$issueData['custom_fields'][] = array('field' => array('id' => '39'),  'value' => $arguments->AO_ID);
			//}
			// check related with alarm temip

			//$result = $client->mc_issue_add( $username, $password, $issueData );
			//save to rabbitMQ
			$deliver_sm = array(
				'url'          => URL_WSDL,
				'username'     => $username,
				'password'     => $password,
				'arguments'    => (array) $arguments,
				'issueData'    => $issueData,
				'action'       => "at_add",
				'isat_id'       => $arguments->indosat_ticket_id,
			);

			if($alarm_id>0){
				$deliver_sm['alarm_id'] = $alarm_id;
			}

			$log_data = "category = Alarm Troubleticket, severity = ".$severity.", status = ".$arguments->status.", Problem = ".$arguments->problem.", Alarm Name = ".$arguments->alarm_name.", start time = $date_submited, ne name = $arguments->ne_name, Indosat Ticket ID = $arguments->indosat_ticket_id, ao_id = ".$arguments->AO_ID;

			error_log( "\n(". date('Y/m/d H:i:s') .") ".  $log_data, 3, '/var/www/html/log/alarmstt'.date('Y-m-d').'.log' );

			$result = saveToRabbitMQ('rs_mc_issue_add', $deliver_sm);

			if($result){
				$api_status = 'SUCCESS';
				$api_description = 'Data Queued Successfully';
			}else{
				$api_status = 'FAILED';
				$api_description = 'Please Check Filed';
			}

			error_log( "\n" .  print_r($api_description, 1), 3, '/var/www/html/log/alarmstt'.date('Y-m-d').'.log' );

		}else{
			$api_description = $api_error;
		}

		return json_encode(array("result" => $api_status, "description" => $api_description));
	}

	/**
	 * Alarm Trouble Ticket Update..
	 *
	 * @param string $username A username
	 * @param string $password A password
	 * @param string $arguments A arguments
	 * @return string Response
	 */
	function at_update($username, $password, $arguments){
		$api_status = "SUCCESS";
		$api_error = "";
		$api_description = "";
		$pwd = array(
			"remedyransharing" => "HI$[`K.f'Fr]DrCY"
		);


		if (empty($username) OR empty($password)) {
			$api_status = "FAILED";
			$api_error = "Invalid credential";
		} elseif (!array_key_exists($username, $pwd)) {
			$api_status = "FAILED";
			$api_error = "Invalid user";
		} else {
			if ($pwd[$username] != $password) {
				$api_status = "FAILED";
				$api_error = "Invalid password";
			}
		}

               //error_log( "\n" .  print_r($arguments, 1), 3, '/var/www/html/log/alarmstt'.date('Y-m-d').'.log' );

		if(empty($api_error)){

			$arguments = json_decode(strtojson($arguments));

			if(strtolower($arguments->status)=='new'){
				$status = 10;
			}else if(strtolower($arguments->status)=='assigned'){
				$status = 50;
			}else if(strtolower($arguments->status)=='in progress'){
				$status = 60;
			/*
			}else if(strtolower($arguments->status)=='pending'){
				$status = 70;
			*/
			}else if(strtolower($arguments->status)=='resolved'){
				$status = 80;
			}else if(strtolower($arguments->status)=='closed'){
				$status = 90;
			}else if(strtolower($arguments->status)=='canceled'){
				$status = 75;
			}

			$arguments->finish_date = $arguments->end_date;

			if( isset($arguments->finish_date) == false )
                        	$arguments->finish_date = date("Y-m-d H:i:s");

			$issueData = array(
				'status'   => array( 'id' => $status ),
				'project'  => array( 'id' =>  3 )
			);

			if( !empty($arguments->alarm_name) ) $issueData['summary'] = $arguments->alarm_name;
			if( !empty($arguments->problem) )    $issueData['description'] = $arguments->problem;
			if( !empty($arguments->steps_to_reproduce) )    $issueData['additional_information'] = $arguments->steps_to_reproduce;
			if( !empty($arguments->severity) )   $issueData['severity']  = array( 'name' => strtolower($arguments->severity) );

			if($status==80){
				$end_time = time();
				$issueData['custom_fields'] = array(
                                	array( 'field' => array('id' => '3'), 'value' => $end_time ),//end_date
                        		array( 'field' => array('id' => '1'), 'value' => 'done' ),//work_status
                        	);
			}

			$deliver_sm = array(
				'url'          => URL_WSDL,
				'username'     => $username,
				'password'     => $password,
				'arguments'    => (array) $arguments,
				'issueData'    => $issueData,
				'searchBy'     => 'request_id', // request_id == indosat_ticket_id
				'searchValue'  => $arguments->indosat_ticket_id,
				'countTimeTracking' => 0, // 0 atau 1
				'action'       => "at_update",
			);

			if($status==80){
				$deliver_sm['countTimeTracking']=1;
			}

			$log_data = "Alarm Name = ".$issueData['summary'].", Problem = ".$issueData['description'].", Message = ".$issueData['additional_information'].", Severity = ".$issueData['severity'].", End Time = ".date('Y-m-d H:i:s', $end_time);

			error_log( "\n(" . date('Y/m/d H:i:s') . ") " . $log_data, 3, '/var/www/html/log/alarmstt'.date('Y-m-d').'.log' );

			$result = saveToRabbitMQ('rs_mc_issue_update', $deliver_sm);

			if($result){
				$api_status = 'SUCCESS';
				$api_description = 'Data Queued Successfully';
			}else{
				$api_status = 'FAILED';
				$api_description = 'Please Check Filed';
			}

			error_log( "\n" .  print_r($api_description, 1), 3, '/var/www/html/log/alarmstt'.date('Y-m-d').'.log' );

		}else{
			$api_description = $api_error;
		}

		return json_encode(array("result" => $api_status, "description" => $api_description));
	}

	/**
	 * Quality Ticket Update..
	 *
	 * @param string $username A username
	 * @param string $password A password
	 * @param string $arguments A arguments
	 * @return string Response
	 */
	function qt_update($username, $password, $arguments){
		$api_status = "SUCCESS";
		$api_error = "";
		$api_description = "";
		$pwd = array(
			"remedyransharing" => "HI$[`K.f'Fr]DrCY"
		);

		if (empty($username) OR empty($password)) {
			$api_status = "FAILED";
			$api_error = "Invalid credential";
		} elseif (!array_key_exists($username, $pwd)) {
			$api_status = "FAILED";
			$api_error = "Invalid user";
		} else {
			if ($pwd[$username] != $password) {
				$api_status = "FAILED";
				$api_error = "Invalid password";
			}
		}

		if(empty($api_error)){

			$arguments = json_decode(strtojson($arguments));

			if(strtolower($arguments->status)=='new'){
				$status = 10;
			}else if(strtolower($arguments->status)=='assigned'){
				$status = 50;
			}else if(strtolower($arguments->status)=='in progress'){
				$status = 60;
			/*
			}else if(strtolower($arguments->status)=='pending'){
				$status = 70;
			*/
			}else if(strtolower($arguments->status)=='resolved'){
				$status = 80;
			}else if(strtolower($arguments->status)=='closed'){
				$status = 90;
			}else if(strtolower($arguments->status)=='canceled'){
				$status = 75;
			}

			$issueData = array(
				'custom_fields' => array(
					array('field' => array('id' => '28'), 'value' => $arguments->indosat_ticket_id),//remedy_id
				)
				//'project' => array( 'id' =>  1 )
			);

			if( !empty($arguments->problem_description) )           $issueData['summary'] = $arguments->problem_description;
			if( !empty($arguments->steps_to_reproduce) )           $issueData['additional_information'] = $arguments->steps_to_reproduce;
			if( !empty($arguments->performance_degradation_type) )  $issueData['description'] = $arguments->performance_degradation_type;
			if( !empty($status) )   $issueData['status']    = array('id' => $status);
			if( !empty($arguments->severity) ) $issueData['severity']  = array( 'name' => strtolower($arguments->severity) );

			$deliver_sm = array(
				'url'          => URL_WSDL,
				'username'     => $username,
				'password'     => $password,
				'arguments'    => (array) $arguments,
				'issueData'    => $issueData,
				'searchBy'     => 'request_id', // request_id == indosat_ticket_id
				'searchValue'  => $arguments->indosat_ticket_id,
				'countTimeTracking' => 0, // 0 atau 1
				'action'       => "qt_update",
			);

			$log_data = "Problem = ".$issueData['summary'].", Message = ".$issueData['additional_information'].", status = ".$arguments->status.", severity = ".$issueData['severity'].", Indosat Ticket ID = ".$arguments->indosat_ticket_id;

			error_log( "\n(" . date("Y/m/d H:i:s") . ") $log_data"  , 3, '/var/www/html/log/qualitiestt'.date('Y-m-d').'.log' );

			$result = saveToRabbitMQ('rs_mc_issue_update', $deliver_sm);

			if($result){
				$api_status = 'SUCCESS';
				$api_description = 'Data Updated Successfully';
			}else{
				$api_status = 'FAILED';
				$api_description = 'Please Check Filed';
			}

			error_log( "\n" .  print_r($api_description, 1), 3, '/var/www/html/log/qualitiestt'.date('Y-m-d').'.log' );

		}else{
			$api_description = $api_error;
		}

		return json_encode(array("result" => $api_status, "description" => $api_description));
	}

	/**
	 * Quality Ticket Proposed To Closed...
	 *
	 * @param string $username A username
	 * @param string $password A password
	 * @param string $arguments A arguments
	 * @return string Response
	 */
	function qt_proposedtoclose($username, $password, $arguments){
		$api_status = "SUCCESS";
		$api_error = "";
		$api_description = "";
		$pwd = array(
			"remedyransharing" => "HI$[`K.f'Fr]DrCY"
		);

		if (empty($username) OR empty($password)) {
			$api_status = "FAILED";
			$api_error = "Invalid credential";
		} elseif (!array_key_exists($username, $pwd)) {
			$api_status = "FAILED";
			$api_error = "Invalid user";
		} else {
			if ($pwd[$username] != $password) {
				$api_status = "FAILED";
				$api_error = "Invalid password";
			}
		}

		if(empty($api_error)){

			$arguments = json_decode(strtojson($arguments));

			$arguments->finish_date = $arguments->end_date;

			if( isset($arguments->finish_date) == false )
				$arguments->finish_date = date("Y-m-d H:i:s");

			//save to rabbitMQ
			$issueData = array(
				'status'        => array('id' => 80),
				'custom_fields' => array(
					array( 'field' => array('id' => '3'), 'value' => strtotime($arguments->finish_date) ),//end_date
					array( 'field' => array('id' => '1'), 'value' => 'done' ),//work_status
				),
				'project' => array( 'id' =>  1 )
			);
			$deliver_sm = array(
				'url'          => URL_WSDL,
				'username'     => $username,
				'password'     => $password,
				'arguments'    => (array) $arguments,
				'issueData'    => $issueData,
				'searchBy'     => 'request_id', // request_id == indosat_ticket_id
				'searchValue'  => $arguments->indosat_ticket_id,
				'countTimeTracking' => 1, // 0 atau 1
				'action'       => "qt_proposedtoclose",
			);

			$log_data = "End Time = ".$arguments->finish_date.", status = Proposed To Closed, Indosat Ticket ID = ".$arguments->indosat_ticket_id;

			error_log( "\n(" . date("Y/m/d H:i:s") . ") " . $log_data, 3, '/var/www/html/log/qualitiestt'.date('Y-m-d').'.log' );
			$result = saveToRabbitMQ('rs_mc_issue_update', $deliver_sm);
			//proposed_to_close_master($arguments->indosat_ticket_id);
			if($result){
				$api_status = 'SUCCESS';
				$api_description = 'Status Change to Proposed to Closed';
			}else{
				$api_status = 'FAILED';
				$api_description = 'Please Check Filed';
			}

			error_log( "\n" .  print_r($api_description, 1), 3, '/var/www/html/log/qualitiestt'.date('Y-m-d').'.log' );

		}else{
			$api_description = $api_error;
		}

		return json_encode(array("result" => $api_status, "description" => $api_description));
	}

	/**
	 * Change Request Proposed To Closed...
	 *
	 * @param string $username A username
	 * @param string $password A password
	 * @param string $arguments A arguments
	 * @return string Response
	 */
	function cr_proposedtoclose($username, $password, $arguments){
		$api_status = "SUCCESS";
		$api_error = "";
		$api_description = "";
		$pwd = array(
			"remedyransharing" => "HI$[`K.f'Fr]DrCY"
		);


		if (empty($username) OR empty($password)) {
			$api_status = "FAILED";
			$api_error = "Invalid credential";
		} elseif (!array_key_exists($username, $pwd)) {
			$api_status = "FAILED";
			$api_error = "Invalid user";
		} else {
			if ($pwd[$username] != $password) {
				$api_status = "FAILED";
				$api_error = "Invalid password";
			}
		}

		if(empty($api_error) && !empty($arguments)){

			$arguments = json_decode(strtojson($arguments));

			$arguments->finish_date = $arguments->end_date;

			if( isset($arguments->finish_date) == false )
				$arguments->finish_date = date("Y-m-d H:i:s");
			//save to rabbitMQ
			$issueData = array(
				'status'          => array('id' => 80),
				'custom_fields'   => array(
					array('field' => array('id' => '3'), 'value' => strtotime($arguments->finish_date) ),//end_date
					array('field' => array('id' => '1'), 'value' => 'done'),//work_status
				),
				'project' => array( 'id' =>  4 )
			);
			$deliver_sm = array(
				'url'          => URL_WSDL,
				'username'     => $username,
				'password'     => $password,
				'arguments'    => (array) $arguments,
				'issueData'    => $issueData,
				'searchBy'     => 'request_id', // request_id == indosat_ticket_id
				'searchValue'  => $arguments->indosat_ticket_id,
				'countTimeTracking' => 1, // 0 atau 1
				'action'       => "cr_proposedtoclose",
			);

			$log_data = "End Time = ".$arguments->finish_date.", status = Proposed To Closed, Indosat Ticket ID = ".$arguments->indosat_ticket_id;

			error_log( "\n(" . date("Y/m/d H:i:s"). ") ".$log_data, 3, '/var/www/html/log/changerequests'.date('Y-m-d').'.log' );

			$result = saveToRabbitMQ('rs_mc_issue_update', $deliver_sm);

			if($result){
				$api_status = 'SUCCESS';
				$api_description = 'Status Change to Proposed to Closed';
			}else{
				$api_status = 'FAILED';
				$api_description = 'Please Check Filed';
			}

			error_log( "\n" .  print_r($api_description, 1), 3, 'log/cr_proposedtoclose_'.date('Y-m-d').'.log' );

		}else{
			$api_description = $api_error;
		}

		return json_encode(array("result" => $api_status, "description" => $api_description));
	}

	function saveToRabbitMQ( $queueName, $deliver_sm ){
		$x = new Jobamqp($queueName);
		return $x->write($deliver_sm, null);
		/*
		$cnn = new AMQPConnection(array(
			//'host' => '202.43.162.164', 'port' => '5672', 'vhost' => '/',
			'host' => '127.0.0.1', 'port' => '5672', 'vhost' => '/',
			'login' => 'guest', 'password' => 'guest'
		));
		$t = $cnn->connect();

		// Create a channel
		$ch = new AMQPChannel($cnn);

		// Declare a new exchange
		$ex = new AMQPExchange($ch);

		$ex->setName('fd3.direct');
		$ex->setType(AMQP_EX_TYPE_DIRECT);

		// Create a new queue
		$q = new AMQPQueue($ch);
		// $q->declare();
		$q->setName($queueName);

		// Bind it on the exchange to routing.key
		$ex->bind('fd3.direct', $queueName);

		// Publish a message to the exchange with a routing key
		return $ex->publish( json_encode($deliver_sm), $queueName);*/
	}

	function strtojson($str){
		$find = array(",\n", '","' , '" , "' ,'", "' , '" ,"', '"'," => ","=> "," =>");
		$replace = array("##", '"##"', '"##"', '"##"', '"##"','',"=>","=>","=>");
		$str = str_replace($find,$replace,$str);

		$exp = explode("##",$str);


		foreach($exp as $arr){
			$ch = explode("=>",$arr);
			$data[$ch[0]] = $ch[1];
		}

		return json_encode($data);
	}

	function mc_get_alarm_id($p_username, $p_password, $status='90'){
		$sql = "SELECT a.value
			FROM  `mantis_custom_field_string_table` a
			JOIN mantis_bug_table b ON a.bug_id = b.id
			AND a.field_id =28
			AND b.project_id =2
			WHERE b.status < '$status'";

		$result = db_query_bound($sql);
                while($id = db_fetch_array ( $result)){
                        $all_id[]['id'] = $id['value'];
                }


                return json_encode($all_id);
	}

function mc_get_monitoring($project_id){
		//CHECK USER ID AND BUG_ID
		$queryfind2= "SELECT user_id FROM executive_users where status = 1  and project_id = '$project_id'";
		$result5 = db_query_bound($queryfind2);
		while($bug = db_fetch_array ( $result5)){
			$monitor[]['id'] = $bug['user_id'];
		}


		return $monitor;

	}
function mc_add_user_monitoring($username, $password, $data){
                //CHECK USER ID AND BUG_ID
                $queryAdd = "INSERT INTO executive_users(user_id,status) values(".$data.",1)";
		//error_log("\n wahyu log " . $queryAdd, 3, '/var/www/html/log/mantis_'.date('Y-m-d').'.log') ;

	        $result = db_query_bound($queryAdd);
        	$row    = db_result( $result );

		return $row;
                //return serialize($data);

        }

function mc_auth_email_checker( $p_username, $p_password, $data ) {
	$queryfind= "SELECT count(a.user_id) as jumlah FROM executive_users a LEFT JOIN mantis_user_table b on a.user_id=b.id where a.status = 1  and a.project_id = '".$data['project_id']."' and b.email = '".$data['email']."'";
	$result = db_query_bound($queryfind);
	$row 	= db_result( $result );

	return $row;
}

